In ThisWorkbook.Module, you have to use the Workbook_Open procedure:

Sub Workbook_Open()

MyFormName.Show

End Sub

Double click on your form dropdown, you will get to vba editor. On top right of the code window, you have a dropdown to select the event you want to use: Change, Enter, Exit, and so on, depending on the trigger you want to use. To the left of this dropdown, you can select the objects from your form, if you want to trigger an action for another field.

Hi Catalin,

thanks for the above....

however, I need the workbook or tab/sheet from the same workbook to open, base on the dropdown. Based on the dropdown it will either open 1 of the two tabs/sheets.

dropdown = cboAsset

dropdown to choose from the above cboAsset is "Data1" or "Data2"

command button - cmdOpen

if "Data1" is chosen from the above dropdown, then I want cmdOpen to go the sheet/tab "Data1RequestForm". If "Data2" is chosen from the above dropdown then I need the cmdOpen to go to sheet/tab "Data2RequestForm", and possibly to open multiple sheets/tabs.

I hope that all makes sense.

thanks

If you want to start the code when there is a change in that dropdown, use:

If you want the code to run from the button:

[...]and possibly to open multiple sheets/tabs.

No idea what this means. Hope you don't want to open them in the same time? That is not possible, of course, not manually or any other way.

Hi 

I tried the below and this is not working....

Private Sub cboAsset_Change()
On Error Resume Next
Application.Goto Code_Testing_Macro_Enabled.Senex_Request(cboAsset.Value & "Senex_Request").Cells(1)

End Sub

then I tried the below and this is also not working......

Private Sub cmdOpen_Click()

On Error Resume Next
Select Case cboAsset.Value
If cboAsset.Value = "Senex" Then

Application.Goto Code_Testing_Macro_Enabled.Senex_Request(cboAsset.Value & "Senex_Request").Cells(1)
End If

Case cboAsset.Value
If cboAsset.Value = "Beach" Then

Application.Goto Code_Testing_Macro_Enabled.Beach_Request(cboAsset.Value & "Beach_Request").Cells(1)

End Sub

how do I fix it please?

What's this code for?

Code_Testing_Macro_Enabled.Senex_Request

Application.Goto needs a range address, should look exactly as provided:

Application.Goto ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Cells(1)

The blue text should be the worksheet name, nothing else should change.

My guess is you don't even need a Select Case.

thats great it is working now

Private Sub cmdOpen_Click()

On Error Resume Next

Application.Goto ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Cells(1)

End Sub

.... how come I only have to enter "_Request" as the form name that it opens up to ? as opposed to the full sheet name of 

"Senex_Request" or "Beach_Request" ?

and just one more question....

once the user hits the command button and it goes to the correct request form (as per above), how can I hide the other tabs/forms, so they are hidden so the user cannot see them or enter data on them by mistake?

how come I only have to enter "_Request" as the form name that it opens up to ? as opposed to the full sheet name of "Senex_Request" or "Beach_Request" ?

What do you mean by : "it opens up to ?" ? It does not matter how the form opens up. When they select something from that combo, that selected value becomes the combo value, not the initial value.

You are building the worksheet name based on the value selected in cboAsset dropdown:

cboAsset.Value & "_Request"

If user selected "Beach" in cboAsset dropdown, cboAsset.Value will be "Beach" , we just have to attach the suffix "_Request" to create the correct sheet name.

Hiding a worksheet:

ThisWorkbook.Worksheets("Sheet1").Visible=xlSheetHidden

Displaying a sheet:

ThisWorkbook.Worksheets("Sheet1").Visible=xlSheetVisible

You have to build your own logic to hide/display.

what I mean when I say "it opens up to" I mean the tab/sheet. sorry if I was not clear.

I don't understand how the suffix "_Request" goes to the correct sheet name. What about if my sheet names had different titles? Like "Request Form 1" and "Request Form 2"?

 I entered the below..... and this is working to hide the sheet, but doesn't differentiate between the values in my dropdown. 

Private Sub cmdOpen_Click()

On Error Resume Next
Application.Goto ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Cells(1)

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetHidden

End Sub

I need it to hide sheet 1 when the user clicks on "Beach" and need it hide sheet 2 when the user clicks on "Senex"

so I tried.

Application.Goto ThisWorkbook.Worksheets(cboAsset.Value = "Senex" & "Senex_Request").Cells(1)

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetHidden

but this didn't work either. Hiding the sheet is working but it is doing it automatically, not off the value in the dropdown.

thanks

I don't understand how the suffix "_Request" goes to the correct sheet name. What about if my sheet names had different titles? Like "Request Form 1" and "Request Form 2"?

I provided an example of how to generate the worksheet name based on the values from the form and based on the values you have indicated. You never said that your sheet names will be like "..Form 1", "..Form 2" so I cannot give you a closer example. You have to build the sheet name based on what you know, it's your design, we have no idea of what you have there, there is no sample file provided.

If you don't have a consistent sheet naming convention that should simplify coding, you will have to generate sheet names using conditional coding:

Dim ShName as string

Select Case cboAsset.Value

Case Is="Beach"

ShName="Request Form 1"

Case is ="Whatever"

ShName="Request Form 3"

Case Else

ShName="Request Form 78"

End Select

Application.Goto ThisWorkbook.Worksheets(ShName).Cells(1)

What you have below cannot work:

ThisWorkbook.Worksheets(cboAsset.Value = "Senex" & "Senex_Request").Cells(1)

Please keep in mind that you have to create a text string, in your code: cboAsset.Value = "Senex" & "Senex_Request" you are making a comparison that does not make any sense between cboAsset.Value and: "SenexSenex_Request".

A comparison will always return true or false, I guess you don't have sheets named "True" ?

ok thanks, I was just trying to understand it. I am not an expert user of VBA. 🙁

so keeping the consistent sheet naming convention, I cannot get the below to work.

I entered the below..... and this is working to hide the sheet, but doesn't differentiate between the values in my dropdown. 

Private Sub cmdOpen_Click()

On Error Resume Next
Application.Goto ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Cells(1)

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetHidden

End Sub

I need it to hide "Senex_Request" when the user clicks on "Beach" and need it hide "Beach_Request" when the user clicks on "Senex"

You are using a static value, hard typed, not a dynamic value for sheet name:

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetHidden

To hide a sheet, build the logic to determine the sheet to be hidden:

Dim SheetToHide as String

ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Visible = xlSheetVisible

if cboAsset.Value="Beach" then 

SheetToHide= "Senex_Request"

ElseIf cboAsset.Value="Senex" then

SheetToHide= "Beach_Request"

End If

if SheetToHide<> "" then ThisWorkbook.Worksheets(SheetToHide).Visible = xlSheetHidden

thanks so much this works perfectly now.

If I wanted to hide 2 sheets instead of 1?

and then make visible a sheet? all based of the value in the dropdown ?

example.

cboAsset.Value = "Beach"

then I want to hide sheet "Senex_Request" and also "DataEntry"

and then

if cboAsset.Value = "Senex"

then I want to hide "Beach_Request" and also "List" but I want to unhide "DataEntry".

how do I write this please?

and sorry one more problem.. I need all the sheets visible on start up.. when the excel file is opened. the way it is at the moment the sheets are hidden until the dropdown value is entered and then it does change.

thanks

ThisWorkbook.Worksheets(cboAsset.Value & "_Request").Visible = xlSheetVisible

if cboAsset.Value="Beach" then 

ThisWorkbook.Worksheets("Senex_Request").Visible = xlSheetHidden

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetHidden

ElseIf cboAsset.Value="Senex" then

ThisWorkbook.Worksheets("Beach_Request").Visible = xlSheetHidden

ThisWorkbook.Worksheets("DataEntry").Visible = xlSheetVisible

ThisWorkbook.Worksheets("List").Visible = xlSheetHidden

End If

To unhide all sheets, remember that in second message I provided this code that runs when the file is open:

In ThisWorkbook.Module, you have to use the Workbook_Open procedure:

Sub Workbook_Open()

MyFormName.Show

End Sub

All you have to do is to put here your list of sheets:

thanks so much for you help... it is all working now like it should.