1

I could really use some help on this. I've read through about 60+ websites and it's either not clicking (pun intended), or it's incorrect for my application. Here's the rundown:

Goal: Use a "Submit" button that was dynamically created in a Userform to copy the Caption from an OptionButton to a dynamic cell on the worksheet, and then clear/close the Userform.

Background: The userform is called from a change in a column in the worksheet. Here's a snippet of the code used to call the userform:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lastRow As Long

    With Worksheets("Test")
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    With Target
        If .Count > 1 Then Exit Sub
        If Not Intersect(Range("B1:B" & lastRow), .Cells) Is Nothing Then
            Application.EnableEvents = False
            If IsEmpty(.Value) Then
                .Offset(0, 1).ClearContents
            Else
                With .Offset(0, 1)
                    .NumberFormat = "mmm dd yyyy hh:mm:ss"
                    .Value = Now
                    UserForm1.Show
                End With
            End If
            Application.EnableEvents = True
        End If
    End With

End Sub

After the Userform is shown, it initializes. It pulls from a list on the spreadsheet to populate how many option buttons there are, their captions, and the dimensions of each item on the Userform. The code for that is this:

Sub UserForm_Initialize()

Dim HLastRow As Integer
Dim NoOfExplanations As Integer
Dim TopPixels As Integer
Dim UserFormHeight As Integer
Dim UserFormWidth As Integer
Dim Opt As Variant
Dim i As Integer
Dim ExplanationRow As Integer
Dim lbl As MSForms.Label
Dim LabelCap As String
Dim btn As CommandButton
Dim OtherInput As MSForms.TextBox
Dim Margins As Integer

    With Worksheets("Test")
        HLastRow = .Cells(.Rows.Count, "H").End(xlUp).Row
    End With

NoOfExplanations = Application.WorksheetFunction.CountA(Worksheets("Test").Range("H2:H" & HLastRow))
Margins = 20

LabelCap = "You have chosen a non sequential row for your team/subteam. Please select an explanation below before you are able to proceed"
UserFormWidth = Len(LabelCap) * 2
TopPixels = (18 * 2)
UserFormHeight = TopPixels + 80 + (20 * NoOfExplanations)

    With UserForm1
        .Width = UserFormWidth + 40
        .Height = UserFormHeight
    End With

    Set lbl = UserForm1.Controls.Add("Forms.Label.1")
    With lbl
        .Top = 10
        .Left = 20
        .Height = 20
        .Width = UserFormWidth - 20
        .Caption = LabelCap
    End With

ExplanationRow = 2
For i = 1 To NoOfExplanations

    Set Opt = UserForm1.Controls.Add("Forms.OptionButton.1", "OptionButton" & i, True)

    Opt.Caption = Worksheets("Test").Cells(ExplanationRow, 8).Value

    If Worksheets("Test").Cells(ExplanationRow, 8).Value = "Other" Then
        Set OtherInput = UserForm1.Controls.Add("Forms.TextBox.1")
        With OtherInput
            .Top = TopPixels
            .Width = UserFormWidth - (Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) * 11)
            .Left = UserFormWidth - (UserFormWidth - (Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) * 11))
            .Height = 18
        End With
    End If

    If Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) > 45 Then
        Opt.Width = UserFormWidth - 10
        Opt.Height = 36
        Opt.Left = 18

        Opt.Top = TopPixels
        TopPixels = TopPixels + 38
    End If

    If Len(Worksheets("Test").Cells(ExplanationRow, 8).Value) <= 45 Then
        Opt.Width = UserFormWidth - 10
        Opt.Height = 18
        Opt.Left = 18

        Opt.Top = TopPixels
        TopPixels = TopPixels + 20
    End If
    ExplanationRow = ExplanationRow + 1
Next i

    Set btn = UserForm1.Controls.Add("Forms.CommandButton.1")
    With btn
        .Top = TopPixels
        .Width = 40
        .Left = ((UserFormWidth + 40) / 2) - 20
        .Height = 20
        .Caption = "Submit"
        .Name = btn
    End With
End Sub

Question: So, how do I get the btn created here in the Userform to both copy the selected OptionButton caption to the dynamic cell, and then clear/close the Userform?

I know it's a stretch, but I'm trying to fill in the cell that is two columns over from the "Target" cell that triggers the Userform to open. The code fills in the current date/time in the .Offset(0, 1) in the Worksheet_Change snipped, but is there a way to place the OptionButton caption in the cell at .Offset(0, 2)?

I'm still pretty new to VBA and this one thing is really sticking a thorn in me.

I'll be incredibly grateful for any help on this.

Thanks! Joe

1 Answer 1

1

Changing your btn variable to a class level variable and using WithEvents will allow you to access the dynamic buttons events.

Private WithEvents btn As CommandButton

Private Sub btn_Click()
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "OptionButton" Then
            If ctrl.Object.Value Then
                MsgBox ctrl.Object.Caption
            End If
        End If
    Next
End Sub

enter image description here

Sign up to request clarification or add additional context in comments.

7 Comments

Thanks a lot Thomas! Sorry if this is a stupid question, but how to I change the btn to a class level variable? I set the code exactly as you have it in the GIF, but it's still not working, so I think I'm missing that piece.
Class variable are declared outside of any sub routines preferably top of the module. Make sure that you don't have a variable with the same name declared in your sub routine.
Ok, awesome. I got it working. That's a good step in the right direction. Thank you so much for the help Thomas!
i usually use witevents as public inside class module. Not sure how tou would set btn if it is as private.
You can make the btn's scope public if you like. But Userforms are class modules. You can have the btn variable at the top of the userform's class module. The btn will available to all methods inside of the userform.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.