userform command button, list box, and check box macro

[still learning]

Hi

I have a userform that has a textbox that the user will enter a name
It also has 3 check boxes that the user will click on ( only 1 for each session)
When the user clicks on the command button, I want the name from the text box to go to a range (cell) and a macro to run …and the text box and check box to empty and the userform to close.
I’m still learning userform macros…. and came up with the following macro.

But I’m getting an error at the first “End If”….Compile error: end if without block if
That stops me from seeing if the rest will work

Sub CommandButton1_Click()
If Me.CheckBox1.Value = True Then
With TextBox1
ActiveSheet.Range("cc2").Value = .Text
End If
end with
macro1
If Me.CheckBox2.Value = True Then
With TextBox1
ActiveSheet.Range("cd2").Value = .Text
End If
end with
macro2
If Me.CheckBox2.Value = True Then
With TextBox1
ActiveSheet.Range("ce2").Value = .Text
End If[
end with
macro3
Unload Me
End Sub

The different macros will match the name in cell and will put that name and the rest of the information in a new range to be viewed
I’ve tested them and they work.
I haven’t been able to get past the compile error to see if the rest of this macro will work



mike

 

[dmt32]

Hi,

Your End With needs to be inside the If Then statement

If Me.CheckBox1.Value = True Then

  With TextBox1

     ActiveSheet.Range("cc2").Value = .Text

  End With

End If

But you probably do not need a With Statement for this requirement, you could write it like this maybe

If Me.CheckBox1.Value = True Then ActiveSheet.Range("cc2").Value = Me.TextBox1.Text

However, if you are allowing only one selection for each session then I suggest that you should be using OptionButtons

Make a backup of your workbook the replace your 3 CheckBoxes with 3 Optionbuttons.

Assuming that they retain their default names (OptionButton1 etc) then see if this update to your code will do what you want.

Sub CommandButton1_Click()
    Dim optSelected As Variant
    optSelected = Application.Match(True, Array(Me.OptionButton1.Value, Me.OptionButton2.Value, Me.OptionButton3.Value), 0)
    If Not IsError(optSelected) Then ActiveSheet.Cells(2, 80 + optSelected).Value = Me.TextBox1.Text: Unload Me
End Sub

You have not shared Macro1, Macro2 & Macro3 being called in your code so cannot determine how my suggested update would work with these codes.

macro to run …and the text box and check box to empty and the userform to close

when the userform Unloads, this action will return each control to its value specified in its property window which, if you have made no changes at design time, will be False for OptionButtons & textbox no value (empty).

As a personal aside, as you develop your project consider giving the controls in the userform meaningful names that follow User Form object naming conventions

This will help make your project easier to read & understand.

Hope Helpful

Dave

 

[still learning]

Hi dmt32,
Thank you for your time and help
I never know about or used optionbuttions ...can't say that again !!!
I made a new Userform and put in a text box, 3 optionbuttons (numbering them 1,2,3), and a command button
I put in your macro in the commandbutton sub and added one of my macros that i want to run after i hit the command button.
AND it worked perfectly. thank you

But, I don't know where to put the other macros that i want to run with button2 and 3

These macros find the data associated with the name from the text box and put it in a range and move the screen to that range
each option button will look at the same data but move the results to a different range....
here is one of the macros ......FYI

Sub anniv()
       'anniversary
 Application.Goto Reference:="R3C23"
    ActiveCell.FormulaR1C1 = "=+RC[59]"
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=+R[1]C[58]"
    Application.Goto Reference:="R1c19 ", Scroll:=True
    noresults1
End Sub

R1C58 and R1C59 are for the results...
in these cells is a =xlookup formula...
R3C23 is where the =xlookup gets its look up value ( a name)...
the rest centers the results on the screen or shows a message box with "no results"...
each optionbutton will have the results shown in a different range...
only 1 optionbutton will be used for each session


mike

 

[dmt32]

But, I don't know where to put the other macros that i want to run with button2 and 3

Sorry not fully clear - do each of these codes only run based on which OptionButton is selected?

Dave

 

[still learning]

Yes Sir
macro 1 goes with optionbutton1
macro 2 goes with optionbutton2
macro 3 goes with optionbutton3
each macro is based on the name in the text box...which doesn't change until the next session

mike

 

[dmt32]

Still not sure that I fully understanding your workflows but to call individual procedures based on OptionButton selection you can try amended code & see if does what you want

Sub CommandButton1_Click()
    Dim optSelected As Variant
    optSelected = Application.Match(True, Array(Me.OptionButton1.Value, Me.OptionButton2.Value, Me.OptionButton3.Value), 0)
    If Not IsError(optSelected) Then
        ActiveSheet.Cells(2, 80 + optSelected).Value = Me.TextBox1.Text
        Select Case Val(optSelected)
            Case 1
               ' macro1
            Case 2
                'Macro2
            Case 3
                'Macro3
        End Select
        Unload Me
    End If
End Sub

Dave

 

[still learning]

Thank you dmt32,
It works perfectly.
each macro works with the intended optionbutton.
Thank you for the help and learning experience

mike

 

[dmt32]

Thank you dmt32,
It works perfectly.
each macro works with the intended optionbutton.
Thank you for the help and learning experience

mike

most welcome & very much appreciate your feedback

Dave