ExcelBanter - Use text from a cell reference in vba code.

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Use text from a cell reference in vba code. (https://www.excelbanter.com/excel-discussion-misc-queries/266012-use-text-cell-reference-vba-code.html)

Use text from a cell reference in vba code.

I have a combobox on the first sheet of a workbook. I’m using it to jump to selected pages as there is close to 100 sheets. All selections in the combobox dropdown menu, except one, are sheet names so that part of the vba code is simple.
I’ve used

Code:

Private Sub ComboBox1_Change()



If ComboBox1.Value = "Statistics" Then

Sheets("Statistics").Select

End If

and so on . . . . .

The exception in the dropdown menu is called ‘Current Day’. Each sheet has a unique date. I want to be able to jump to the sheet associated with the current day.
I’m using the ‘vlookup’ function followed by ‘Today()’ to give me the sheet name relating to the current date in cell ‘Sheet 1!$AH$130’.
The combobox linked cell is ‘Sheet1!$AE$128’

Could someone please give me a few pointers, as I am unsure of where to go from here.
Thanks.

Quote:

Originally Posted by JackyJ (Post 959605)

Code:

Private Sub ComboBox1_Change()



If ComboBox1.Value = "Statistics" Then

Sheets("Statistics").Select

End If

Hi,

Probably easier to use an exclusion for the values that are NOT Sheet Names.

Then replace your sheet finder code with

Sheets(ComboBox1.value).select

or similar.

David