Changing function input from Cell dropdown to Combobox

[tomsweddy]

Hi,

I have the following issue.

I currently have a cell dropdown with names of the worksheets within my workbook. They all start hidden when the workbook is first opened (expect the INPUT sheet). The user can then use this dropdown to select a worksheet they wish to view and click a button (Sub ViewContract) to make the selected worksheet visible.

Code:

[vba]Sub ViewContract()

Select Case Range("K12").Value ' or Select Case Activecell.Value

Case "SELECT": Call SheetVisibility(False, False, False, False, False, False, False, False, False)
Case "New Malden - Manager": Call SheetVisibility(True, False, False, False, False, False, False, False, False)
Case "New Malden - Staff": Call SheetVisibility(False, True, False, False, False, False, False, False, False)
Case "Sudbury Process": Call SheetVisibility(False, False, True, False, False, False, False, False, False)
Case "Sudbury Staff/Man": Call SheetVisibility(False, False, False, True, False, False, False, False, False)
Case "Wisbech Process": Call SheetVisibility(False, False, False, False, True, False, False, False, False)
Case "Wisbech Staff/Man": Call SheetVisibility(False, False, False, False, False, True, False, False, False)
Case "Aintree Process": Call SheetVisibility(False, False, False, False, False, False, True, False, False)
Case "Aintree Staff/Man": Call SheetVisibility(False, False, False, False, False, False, False, True, False)
Case "Factory Grade 4+": Call SheetVisibility(False, False, False, False, False, False, False, False, True)

End Select
End Sub


Private Function SheetVisibility(sh1 As Boolean, sh2 As Boolean, _
sh3 As Boolean, sh4 As Boolean, _
sh5 As Boolean, sh6 As Boolean, _
sh7 As Boolean, sh8 As Boolean, _
sh9 As Boolean)

Application.ScreenUpdating = False
Worksheets("NewMaldenMan").Visible = xlSheetVeryHidden
Worksheets("NewMaldenStaff").Visible = xlSheetVeryHidden
Worksheets("SudburyProcess").Visible = xlSheetVeryHidden
Worksheets("SudburyStaffMan").Visible = xlSheetVeryHidden
Worksheets("WisbechProcess").Visible = xlSheetVeryHidden
Worksheets("WisbechStaffMan").Visible = xlSheetVeryHidden
Worksheets("AintreeProcess").Visible = xlSheetVeryHidden
Worksheets("AintreeStaffMan").Visible = xlSheetVeryHidden
Worksheets("FactGrade4+").Visible = xlSheetVeryHidden
If sh1 Then Worksheets("NewMaldenMan").Visible = xlSheetVisible
If sh2 Then Worksheets("NewMaldenStaff").Visible = xlSheetVisible
If sh3 Then Worksheets("SudburyProcess").Visible = xlSheetVisible
If sh4 Then Worksheets("SudburyStaffMan").Visible = xlSheetVisible
If sh5 Then Worksheets("WisbechProcess").Visible = xlSheetVisible
If sh6 Then Worksheets("WisbechStaffMan").Visible = xlSheetVisible
If sh7 Then Worksheets("AintreeProcess").Visible = xlSheetVisible
If sh8 Then Worksheets("AintreeStaffMan").Visible = xlSheetVisible
If sh9 Then Worksheets("FactGrade4+").Visible = xlSheetVisible
Application.ScreenUpdating = True

End Function[/vba]

I now want to change this so that I can get this function to work from a Excel Combobox rather than having to have the combination of a Cell Dropdown and a Button. Could this be achieved using the a Combobox and the OnSelectedIndexChanged event.....?



Thanks

[tomsweddy]

Can Anyone help with this~?

[p45cal]

Add a combobox from the Control Toolbox toolbar, right click on it and choose Properties, type in the range where the 'cell dropdown' (I'm guessing this was Data Validation (unless this is a Forms toolbar combobox, in which case see below)) previously got its range from. Double click on the control and it should take you to the change event code where you type in your code, to start with keep it simple and put a single line of code in to test it, such as:
MsgBox ComboBox1.Value & " selected"
(substituting the correct name for the combobox, of course).
Back to the sheet, take Excel out of design mode and test.

ActiveX controls (which the above is) are sometimes not very predictable embedded on a sheet, so you could use a combobox from the Forms toolbar instead. Here you would right click it and choose Format Control, and there you'd select your range of values to choose from in the Input range: field on the Control tab, and click OK. Then right click on the combobox again, this time choosing Assign Macro, and a dialogue box should come up with a suggested macro name, it might be something like Book1!DropDown2_Change
then choose Edit (or Record and select a few cells before stopping the recording) then edit the macro. When editing the macro, test it with something simple such as:

[vba]Sub DropDown2_Change()
Set Sht = Sheets("Sheet1") ' the sheet it's on
Set cbox = Sht.Shapes("Drop Down 2").ControlFormat
'MsgBox cbox.ListIndex & " selected"
MsgBox Sht.Range(cbox.ListFillRange)(cbox.ListIndex).Value
End Sub
[/vba]By the way, you can get the name of the combobox by right clicking on it 'til you see its grab handles, then in the immediate pane type:?selection.name
(I don't normally work with Forms toolbar objects so this is probably not the cleverest way to get the selected value.)
Then test it.

If all seems to work, substitute your code adapted to suit.

[tomsweddy]

Thanks but can you define the variables for me as getting syntax errors....

[p45cal]

See attached