Solved: 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.

1. re: 'and so on . . . .'
Have you got lots of If statements like that (where the sheet name really is exactly the same as the combobox value)? If so, for those:
[vba]Sheets(combobox1.Value).Select[/vba] could be shorter.

2. For the exceptions:
[vba]Select Case combobox1.Value
Case "Current Day"
Sheets(Format(Date, "dd/mm/yy")).Select
'Case "Another exception"
'Sheets(… …).select
Case Else 'to deal with the majority of cases:
Sheets(combobox1.Value).Select
End Select
[/vba]
But to get this right we need to know how the sheets have been named with dates in order to construct the name properly; is it '24th May 2010', or '240510', or 'SomeText 24 05 10 SomeMoreText' etc.?
I doubt you'll need to use vlookup.

For part 1. Your right. I repeat it a dozen times, so what you have there is far better.

For part 2. The sheets in question are named 'Day 1' through 'Day 80'.
On each, the date is found on $O$2 with the format 14 March 2010, but I can change that if needed.

Thanks for your time.

Assuming that the dates on all the cells O2 are true excel dates then something like this ought to do it (untested):[vba]Select Case ComboBox1.Value
Case "Current Day"
For Each sht In ThisWorkbook.Sheets
If sht.Range("O2").Value = Date Then
sht.Select
Exit For
End If
Next sht
Case Else
Sheets(ComboBox1.Value).Select
End Select
[/vba]The format of the dates in O2 will be irrelevant.

Much appreciated. That works perfectly.

A new problem now raises it's ugly head.
Regardless of what sheet I'm on, I am unable to jump to the sheet that has it's name preselected in the combobox (this name is showing before you open the dropdown menu). This goes for both the 'Current day' and 'Sheet' names.
Am I correct in saying the combobox needs to be cleared whenever a new sheet is selected?
Are you able to assist here?

Thanks again for you help.

That'd be because you're using a change event and the combobox value is not changing. So yes, you need to change it again to reset it, but without it trying to select another sheet. One way:[vba]Private Sub ComboBox1_Change()
Static Blocked
If Blocked Then Exit Sub
Blocked = True
Select Case ComboBox1.Value
Case "Current Day"
For Each sht In ThisWorkbook.Sheets
If sht.Range("O2").Value = Date Then
sht.Select
Exit For
End If
Next sht
Case Else
Sheets(ComboBox1.Value).Select
End Select
Me.ComboBox1.Value = "" ' or "Choose Sheet" or…
Blocked = False
End Sub
[/vba]Blocked is a Static variable, which means its value is remembered between calls.

I'm now getting a 'Runtime error 40036 when I click on 'Current Day' in the dropdown menu. When I select 'Debug' in the Error window it highlights the following line of code

If sht.Range("O2").Value = Date Then

All other selections work fine.
Thanks for all your input.

Could you post the actual code you're using (copy/paste from your vbe)?

Code:

---

Private Sub ComboBox1_Change()
    Static Blocked
    If Blocked Then Exit Sub
    Blocked = True
    Select Case ComboBox1.Value
    Case "Current Day"
        For Each sht In ThisWorkbook.Sheets
            If sht.Range("O2").Value = Date Then
                sht.Select
                Exit For
            End If
        Next sht
    Case Else
        Sheets(ComboBox1.Value).Select
    End Select
    Me.ComboBox1.Value = "" ' or "Choose Sheet" or…
    Blocked = False
End Sub

---

Well I've now tested it here and it works fine.
The next step is to send me a file. Private Message me here at this site for an email address if you want to do this.

What version of Excel are you using?

You are correct.
Code works well.
Thanks.

I'm using Excel 2007.

I have just come across a problem relating to the resulting code from this thread.
I hope I’m not doing the wrong thing posting here, after a solution had been found.
The following code works beautifully on its own.

Code:

---

Private Sub ComboBox1_Change()
      Static Blocked
      If Blocked Then Exit Sub
      Blocked = True
      Select Case ComboBox1.Value
      Case "Current Day"
          For Each sht In ThisWorkbook.Sheets
              If sht.Range("O2").Value = Date Then
                  sht.Select
                  Exit For
              End If
          Next sht
      Case Else
          Sheets(ComboBox1.Value).Select
      End Select
      Me.ComboBox1.Value = "" ' or "Choose Sheet" or…
      Blocked = False
  End Sub

---

I then added code which hides and unhides selected sheets. This code courtesy of xld.

Code:

---

  Private Sub ToggleButton1_Click()
      Dim i As Long
     
      For i = 1 To 7
         
          Worksheets("Day " & i).Visible = Not Worksheets("Day " & i).Visible
      Next i
     
  End Sub

---

The problem is everything works well, unless the ‘Current Day’ sheet from the first bit of code, happens to be hidden by the second piece of code.

I would like to keep both pieces of code.
Is this an easy fix, or am I asking too much?

Thanks again.

Untested:[vba]Private Sub ComboBox1_Change()
Static Blocked
If Blocked Then Exit Sub
Blocked = True
Select Case ComboBox1.Value
Case "Current Day"
For Each sht In ThisWorkbook.Sheets
If sht.Range("O2").Value = Date Then
If Not sht.Visible Then sht.Visible = True
sht.Select
Exit For
End If
Next sht
Case Else
'and perhaps to cater for other sheets not being visible when you want to select them:
If Not Sheets(ComboBox1.Value).Visible Then Sheets(ComboBox1.Value).Visible = True
Sheets(ComboBox1.Value).Select
End Select
Me.ComboBox1.Value = "" ' or "Choose Sheet" or…
Blocked = False
End Sub[/vba]

When ‘Current Day’ is selected from the combobox and the sheet pertaining to the ‘Current Day is hidden, it becomes unhidden. (This first part works beautifully)


The problem is if I then unhide that week, the rest of that week’s tabs appear, whilst the ‘Current Day’ tab disappears. If I keep toggling, it swaps between showing the tabs for the other 6 days of that week (hiding the tab for the ‘Current Day’) and showing only the ‘Current Day’ tab (hiding the tabs for the other 6 days of the week).

Note: Each week has a toggle button to hide/unhide that particular week.

This can then be reversed if I again select the ‘Current Day’ from the combobox after the week in question has been unhidden. This action puts the ‘Current Day’ back in sequence with the other days of the week when hiding and unhiding.

Thanks for all your effort

Ha ha ha!
I'm going to sleep on it.
I'm thinking along the lines of either adjusting what the toggle buttons do - perhaps looking at the hidden status of most of that week's sheets before deciding what to do with all the week's sheets, or perhaps, when you choose Current Day, all that week's sheet unhide but the current day's sheet is the active one, or… ?

Suggestions?

'night 'night.

Here's to pleasant, yet productive dreams.

From the name of the event handler (Private Sub ToggleButton1_Click()) I'd love to hope that the buttons you've used really are Toggle buttons - they appear depressed after one click, then click again and they appear undepressed?

If this is the case then the button itself holds a true/false value to indicate its state to the code. So the toggle button's code can be shortened:
[vba]Private Sub ToggleButton1_Click()
Dim i As Long
For i = 1 To 7
Worksheets("Day " & i).Visible = ToggleButton1
Next i
'ToggleButton1.Caption = "Week nn sheets " & IIf(ToggleButton1, "Showing", "Hidden")
End Sub[/vba]I've added a commented-out line to show how you can change the caption on the button too.

I'm definitely using a Toggle Button. The line that you changed

Code:

---

Worksheets("Day " & i).Visible = ToggleButton1

---

brings up a Run-time error '1004' message. 'Application-defined or object-defined error'.

Would it be better if I send you a file?

Thanks

btw, the caption changing is excellent.

Yes, because I have tested his code.
Private Message sent to you at this site to give you an email address.

that should read: 'I have tested this code'

Quote:

---

Originally Posted by p45cal

Yes, because I have tested his code.
Private Message sent to you at this site to give you an email address.

---

Here is the solution to my problem, thanks to p45cal.
It all works beautifully.

The first peice of code (placed in a module) . . .

Code:

---

Sub ShowHideWeek(Start, Finish, TBtn)
Application.ScreenUpdating = False
For i = Start To Finish
  Worksheets("Day " & i).Visible = CBool(TBtn)
Next i
TBtn.Caption = " Week/" & IIf(TBtn, "Hide", "Show")
Application.ScreenUpdating = True
End Sub

---

. . .hides and unhides selected tabs based on the following code .

Code:

---

Private Sub ToggleButton1_Click()
ShowHideWeek 1, 7, ToggleButton1
End Sub

---

Secondly, the following piece of code (placed in a module) allows you to jump to selected sheets found in a combobox.

Code:

---

Sub SelectASheet(CmboBox)
Static Blocked
If Blocked Then Exit Sub
Blocked = True
Select Case CmboBox.Value
  Case "Current Day"
    For Each sht In ThisWorkbook.Sheets
      If sht.Range("O2").Value = Date Then
      sht.Visible = True
        sht.Select
        Exit For
      End If
    Next sht
  Case Else
    Sheets(CmboBox.Value).Select
End Select
CmboBox.Value = ""  ' or "Choose Sheet" or…
Blocked = False
End Sub

---

Thanks again to p45cal. Your help has been invaluable.