• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Form: Using Combobox & Checkbox

inddon

Member
Hello There,

I have a UserForm with the following objects:
1. CheckBox
2. ComboBox

In worksheet 'One', there is a button 'Menu'. On click, it takes to the Userform.


The required functionality is as follows in the Userform:



1. On worksheet 'One', columns A to D are shown and hidden. This is based on the checkbox (CheckBoxShowColumns). In the Userform, when the User checks the Checkbox , and clicks OK (Button), then it should show columns A to D. When it is Unchecked, then it should unhide the columns A to D

2. In the ComboBox, it should populate the names of the Worksheet in the current workbook. The User selects a value from the combobox and when he click OK (Button) then it should navigate to the worksheet that is selected in the combobox.

I tried to do this in VBA, but it doesn't work properly.

Please find attached the workbook for your reference. Looking forward for your help and advise.




Regards
Don
 

Attachments

  • UserForm - CheckBox and Navigation.xlsm
    17.6 KB · Views: 23
Hi,

Use the below codes in the user form

Code:
Private Sub CommandButtonOK_Click()
 
shtname = ComboBox1.Value
 
If CheckBoxShowColumns = True Then
Sheets("One").Columns("A:D").EntireColumn.Hidden = False
 
Else
Sheets("One").Columns("A:D").EntireColumn.Hidden = True
 
End If
 
Sheets(shtname).Select
 
End Sub
 
 
 
Private Sub UserForm_Activate()
 
Dim sht As Worksheet, txt As String
ComboBox1.Clear
 
For Each sht In ActiveWorkbook.Worksheets
      ComboBox1.AddItem sht.Name
Next sht
 
ComboBox1.ListIndex = 0
 
End Sub
 
Hi,

Use the below codes in the user form

Code:
Private Sub CommandButtonOK_Click()

shtname = ComboBox1.Value

If CheckBoxShowColumns = True Then
Sheets("One").Columns("A:D").EntireColumn.Hidden = False

Else
Sheets("One").Columns("A:D").EntireColumn.Hidden = True

End If

Sheets(shtname).Select

End Sub



Private Sub UserForm_Activate()

Dim sht As Worksheet, txt As String
ComboBox1.Clear

For Each sht In ActiveWorkbook.Worksheets
      ComboBox1.AddItem sht.Name
Next sht

ComboBox1.ListIndex = 0

End Sub


Hello Sathish,

Thank you for your quick reply and help.

I tried to insert the code in the form, confused where exactly to place and call.

Is it possible, if you could please send me the code in the workbook.


Many thanks & regards
Don
 
Hi,

Just double click on your user form in the vba screen

You will be seeing the below procedure. Just delete it and paste the above posted code

PrivateSub UserForm_Click
End Sub
 
Back
Top