Open WS based on a cell value (E2003 SP3)

[steveh]

Good morning all

I have a cell that derives its value from a formula, ideally I would like a worksheet that has the same name as the result to open, I would also like to do it without VBA but I doubt if that is possible. The solution I am working on at the moment involces a Combobox and the following code which is not working

Code: Select all

Private Sub ComboBox4_Change()

    If ComboBox4.Value = "14-Litre" Then
    Sheets("14-Litre").Select
End If

    If ComboBox4.Value = "19-Litre" Then
    Sheets("19-Litre").Select
End If

    If ComboBox4.Value = "47-Litre" Then
    Sheets("47-Litre").Select
End If

End Sub

I have noticed, and this is probably me being daft, that if I click on the design button and then on the Combobox it says =EMBED("Forms.ComboBox.1","") but when I click on the Properties icon it says the name is Combobox4.

Any ideas please

[StuartR]

...if I click on the design button and then on the Combobox it says =EMBED("Forms.ComboBox.1","") but when I click on the Properties icon it says the name is Combobox4...

I can't solve your problem, but I can explain this one. The object being embedded is of type "Forms.ComboBox.1", the EMBED does not display the object name, which is Combobox4.

[steveh]

Thanks Stuart

I had suspected something like that but was not sure

[HansV]

Try

Code: Select all

Private Sub ComboBox4_Change()
    On Error Resume Next
    Sheets(ComboBox4.Value).Select
End Sub

The On Error line suppresses the error message that would appear if the user clears the combo box.

[steveh]

Hi Hans

Thank you but nothing seems to be happening, is your code supposed to replace all of mine or should I be addinf my particular sheets into the select part, I have tried various things but nothing changes the selected page does not happen

[HansV]

It works for me, see the attached very simple example. If you can't get it to work in your workbook, could you attach (a stripped down copy of) it?

ComboChange.xls

[steveh]

Thanks Hans

with my usual amount of buffonery I had put averything into a module, looking at your example has shown me the error of my ways and everything is now working as it should.