Help: Go to this page, based on this cells value

[eldridge.michael]

Good morning all,


I apologize, but my knowledge of VBA programming is slim to none. But, I am hoping you can help solve this simple question for me.


I am trying to make a macro that brings the user to a sheet with the same workbook based on a value in a cell.


For example. I have a combo box (cell link "a1") set up, so that the users can look at different jobs on the dashboard page. We can call them job 1, job 2, job 3. But, if the user wants to look at the details of that job, the click on a button to bring them to the details of that job.


So the static argument is

Sheets("Job 1").Select

. I'm just not sure how to make it dynamic based on change cell data from the combo boxes cell link.

 

[Faseeh]

Hi eldridge.michael,


Just like you my VBA knowledge is also not exhaustive but i think you can introduce a variable that depends on the value (Job) a person select and then you can introduce this:

Application.Worksheets("Job " & i).Select

where 'i' is a variable that gets value from the Job selected.


Regards,

 

[NARAYANK991]

Hi Michael ,


When you say static , you mean that the name of the sheet to be activated is specified within the procedure ?


If so , to make this dynamic , what you need to do is to use the selection done in the combo box as the name of the sheet to be activated.


The combo box selection is available as ComboBox1.Text , assuming that your combo box is named ComboBox1 ; if it named XYZ , then the statement will be XYZ.Text


The combo box will have an event procedure called ComboBox1_Change or XYZ_Change ; within this procedure , you can have a statement such as :


ThisWorkbook.Sheets(ComboBox1.Text).Activate


There is no need to use the LinkedCell , though you can use it ; the statement to be used will be :


ThisWorkbook.Sheets(Range(ComboBox1.LinkedCell).Value).Activate


Narayan

 

[kevincoughlin]

cmdButton_click()

dim aCellvalue as string

acellvalue = sheetname.range("A1").value

select case acellvalue

case "JOB 1" : sheets("JOB 1").activate

case "JOB 2" : sheets("JOB 2").acitvate

case else : 'something else

end select

End Sub