PDA

View Full Version : Solved: Use text from a cell reference in vba code.



JackyJ
09-18-2010, 09:00 PM
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


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.

p45cal
09-19-2010, 06:12 AM
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:
Sheets(combobox1.Value).Select could be shorter.

2. For the exceptions:
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

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.

JackyJ
09-20-2010, 02:06 PM
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.

p45cal
09-20-2010, 03:02 PM
Assuming that the dates on all the cells O2 are true excel dates then something like this ought to do it (untested):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
The format of the dates in O2 will be irrelevant.

JackyJ
09-21-2010, 01:03 AM
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.

p45cal
09-21-2010, 03:42 AM
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: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
Blocked is a Static variable, which means its value is remembered between calls.

JackyJ
09-22-2010, 01:36 PM
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.

p45cal
09-22-2010, 01:40 PM
Could you post the actual code you're using (copy/paste from your vbe)?

JackyJ
09-22-2010, 02:07 PM
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

p45cal
09-22-2010, 03:27 PM
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?

JackyJ
09-24-2010, 04:50 PM
You are correct.
Code works well.
Thanks.

I'm using Excel 2007.

JackyJ
09-25-2010, 01:17 AM
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.

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.



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.

p45cal
09-25-2010, 02:09 AM
Untested: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

JackyJ
09-25-2010, 03:41 PM
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

p45cal
09-25-2010, 04:26 PM
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.

JackyJ
09-26-2010, 12:39 AM
Here's to pleasant, yet productive dreams.

p45cal
09-26-2010, 04:13 AM
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:
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 SubI've added a commented-out line to show how you can change the caption on the button too.

JackyJ
09-27-2010, 01:58 PM
I'm definitely using a Toggle Button. The line that you changed


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.

p45cal
09-27-2010, 02:22 PM
Yes, because I have tested his code.
Private Message sent to you at this site to give you an email address.

p45cal
09-27-2010, 03:06 PM
that should read: 'I have tested this code'


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

JackyJ
09-29-2010, 03:35 AM
Here is the solution to my problem, thanks to p45cal.
It all works beautifully.

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

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 .


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.


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.