Combo Box w/ Links Revert to Default Text

[dgold82]

I have a control combo box on top of each sheet that links to other sheets.
Is there a way to make it revert back to default text. Here is what my code
looks like now:

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Report1" Then
Sheets("sheet1").Select
End If

If ComboBox1.Value = "Report2" Then
Sheets("sheet2").Select
End If

End Sub

I want the combo box to revert back to something like "click here to jump to
reports" after a user clicks "Report2" or "Report1". The problem we are
having is that if a user goes back and fourth between sheets that have this
combo box you can't click on "Report1" to jump back again unless you click
somewhere else first. Hope this makes sense.

Thanks.

(If someone can help me manipulate the code to go to the sheet AND to a
specific cell in that sheet that would be a bonus)

 

[LOFE]

You should just be able to add

ComboBox1 = "This is new Text"

before the Sheet Select statement.

I'd use ElseIf to, just to cut down on lines of code:

If ComboBox1.Value = "Report1" Then
ComboBox1 = "This is new text"
Sheets("Sheet1").Select
Elseif ComboBox1 = "Report2" Then
ComboBox1 = "This is new text"
Sheets("Sheet2").Select
End If

To go to a particular cell, just add the cell reference:
If ComboBox1.Value = "Report1" Then
ComboBox1 = "This is new text"
Sheets("sheet1").Select
Range("A3").Select
Elseif ComboBox1 = "Report2" Then
ComboBox1 = "This is new text"
Sheets("Sheet2").Select
Range("A3").Select
End If

I don't think you need to have the .Value on the statement either.

 

[dgold82]

Thank you! One little thing though. I am getting an error on the
"Range("A3").Select" for other sheets within the workbook. Only the active
sheet range works so if I use the drop down from within sheet 1 to go to
sheet 1 the cell selects. How do I point the code to a different worksheet.

 

[LOFE]

I'm not sure I follow. If you select a new sheet and then the range, it
should just select that new cell in the new sheet. You need to make sure
that the code has got the different sheet selected from the action. Can you
send the code that you have?