Solved: Select sheet based a combobox value

[Aussiebear]

I currently have a workbook consisting of sheets named after a series of storage locations. I am also using a form with a combobox to select a location, which I then hope to use as the value to select a worksheet.

Can I write something like this?
[vba]
If Trim (Me.cboLocation.text) = "" Then
Me.cboLocation.setFocus
MsgBox "Please enter a Location"
Exit Sub
Else

If Trim(Me.cboLocation.text) = "Commodity Shed" Then
Sheet ("Commodity Shed").Select
Sheet("Commodity Shed").Activate
Else
If Trim (Me.cboLocation.text) = "Turkey's Nest" Then
Sheet ("Turkey's Nest").Select
Sheet("Turkey's Nest").Activate
Else
If Trim(Me.cboLocation.text) = "West Q Alley" Then
Sheet ("West Q Alley").Select
Sheet ("West Q Alley").Activate
End If
End If
End If
End If
[/vba]

Will firstly select the sheet as indicated in the combo box and in turn will it then make it the active sheet?

Ted

[JimmyTheHand]

In my experience there's no difference between Sheet.Activate and Sheet.Select. Any one of them is sufficient.

I guess (hope?) you use the rowsource and controlsource properties of the combobox. If so, then you might find the following code useful:

[vba] Private Sub CommandButton1_Click()
On Error GoTo NoMatch
If Application.WorksheetFunction.Match(Range(ComboBox1.ControlSource), Range(ComboBox1.RowSource), 0) > 0 Then
Sheets(Range(ComboBox1.ControlSource).Value).Activate
Exit Sub
End If
NoMatch:
MsgBox ("Sheet doesn't exist")

End Sub[/vba]
EDIT:
A little explanation is due.
The code above uses the worksheetfunction called "MATCH" to look up the current text of ComboBox1 in it's rowsource. In other words, it examines whether the text in the combobox is chosen from the list or entered manually. If it's entered manually, then MATCH goes to error, so the message "Sheet doesn't exist" appears. If it's from the list, then the chosen sheet will be activated.

[Aussiebear]

Yes use rowsource to dicate the choice of locations available, but I need to know from what I have posted above, will it select the correct sheet as indicated by the value selected in the combobox?

Once I get past this point, I will be trying to build up a write to a section on the selected sheet.

Am I on the right track if I simply remove the line/s which are

[vba]
Sheet ("SheetName").Activate
[\vba]

I don't need another command button

[JimmyTheHand]

Yes, you are on the right track.
But that's quite easy to check, you just have to test your own code.

I didn't recommend another command button. Merely showed a 4-line code to check whether the text of the combobox is from the list or not. If you will have a dozen sheets, or change sheet names in the future, perhaps you will see the point.

[Zack Barresse]

There is a very big difference between Select and Activate actually.

When you Select something, you can do a multi-select. If another sheet is activated, you can select another one. When you activate a sheet, you disallow multi-select. This can be advantageous when dealing with multiple sheets at once.

Take this code for example...

[vba]Sub SelectSheets()
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
Dim cb As CheckBox, arrSheets() As String, wsCurr As Object
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then
SheetCount = SheetCount + 1
ReDim arrSheets(1 To SheetCount)
arrSheets(SheetCount) = CurrentSheet.Name
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
TopPos = TopPos + 13
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
PrintDlg.Buttons.Left = 240
With PrintDlg.DialogFrame
.Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
.Width = 230
.Caption = "Select sheets to print"
End With
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
If cb.Text = "Print All" Then
Set wsCurr = ActiveSheet
Sheets(arrSheets).Select
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False
wsCurr.Activate
Exit For
End If
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut
End If
Next cb
End If
Else
MsgBox "All worksheets are empty."
End If
Application.DisplayAlerts = False
PrintDlg.Delete
Sheets("Corp").Select
End Sub[/vba]

You see a big difference between Select and Activate here.

HTH

[JimmyTheHand]

Hi Zack

I see your point.
So what do you suggest, which method should be used in Aussiebear's particular case? Select or Activate?

I studied your code, interesting. I've almost forgot about good old DialogSheets. I've not used such things since... Office 97. And built-in help is not very enthusiastic in propagating them, either... Took me some time to grasp it, but one question remains. Could you please tell me why these lines are needed?
[vba] PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
[/vba] I see no difference between when they are executed and when they are not.

I've also found one error. Maybe an older version of Excel takes it well, but in V.2003 code execution breaks on
[vba]Sheets(arrSheets).Select[/vba] because
[vba]ReDim arrSheets(1 To SheetCount)[/vba] clears all previously set values from the array. You may want to update this in your set of code examples.

Jimmy

[Zack Barresse]

Ah, that should be Redim Preserve, thanks for the catch.

As for AB case, I would use Activate. I always use Activate unless I am multi-selecting a sheet. I would even think about disabling events while I'm doing it, as that will fire the SheetActivate event. Depending on the workbook structure, this could be a good thing or a bad one. Either way, it is a variable worth considering.

As far as the buttons go, I honestly don't remember why they were there. This code originated from Aaron Blood, I just added the button to print all, as it was a request from the MrExcel board on a recent thread (which is why I used it for an example).

[Aussiebear]

Thanks Jimmy. I will be using a predefined known number of sheets which are named as a range and called in rowsource properties of the combo box.

Thanks Zack. I didn't follow your code but I will use the Sheet("Sheet Name").Activate as suggested.

Ted

[Zack Barresse]

AB, here is a commented piece of code...

[vba]Sub SelectSheets()

'Dimension all variables
Dim i As Long, TopPos As Long, SheetCount As Long
Dim PrintDlg As DialogSheet, CurrentSheet As Worksheet
Dim cb As CheckBox, arrSheets() As String, wsCurr As Object

'Turn off screen flickering
Application.ScreenUpdating = False

'Check if workbook is protected, exit if so
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

'Set variables
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
SheetCount = 0
TopPos = 40

'Loop through all worksheets
For i = 1 To ActiveWorkbook.Worksheets.Count

'Reset the varaible 'CurrentSheet'
Set CurrentSheet = ActiveWorkbook.Worksheets(i)

'Check if there is any data on the worksheet and if it is visible
If Application.CountA(CurrentSheet.Cells) <> 0 And CurrentSheet.Visible Then

'Add one to the variable if we know it is a good sheet
SheetCount = SheetCount + 1

'Add one to the array, preserving all past values
ReDim Preserve arrSheets(1 To SheetCount)

'Set the current iteration (of the array) value
arrSheets(SheetCount) = CurrentSheet.Name

'Add the checkbox and name it the name of the valid worksheet
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = CurrentSheet.Name

'Adjust the top position of the form, keeping uniform spacing
TopPos = TopPos + 13

End If

Next i

'Add a little more for the top position, separating the Print All button from the rest
TopPos = TopPos + 13

'Add the Print All button
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount + 1).Text = "Print All"
PrintDlg.Buttons.Left = 240

'Set the area for the dialog sheet/form
With PrintDlg.DialogFrame
.Height = Application.WorksheetFunction.Max(68, PrintDlg.DialogFrame.Top + TopPos - 15)
.Width = 230
.Caption = "Select sheets to print"
End With

'Bring buttons to the front (unkown if needed???)
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
CurrentSheet.Activate

'Turn screen updating back on
Application.ScreenUpdating = True

'Check if there were any valid sheets
If SheetCount <> 0 Then

'If the dialog sheet was created..
If PrintDlg.Show Then

'Loop through each checkbox
For Each cb In PrintDlg.CheckBoxes

'Test if the checkbox value was checked or not
If cb.Value = xlOn Then

'Check if the checkbox value was the Print All button
If cb.Text = "Print All" Then

'Set sheet to return to
Set wsCurr = ActiveSheet

'Select all sheets in the array which met the conditions
Sheets(arrSheets).Select

'Print out selected sheets
ActiveWindow.SelectedSheets.PrintOut copies:=1, preview:=False

'Re-activate the last active sheet
wsCurr.Activate

'Exit sheet at end of Print All routine
Exit For

End If

'If not the Print All button, activate the sheet and print it
Worksheets(cb.Caption).Activate
ActiveSheet.PrintOut

End If

Next cb

End If

Else

'If no sheets were counted, give a message box
MsgBox "All worksheets are empty."

End If

'Turn off events to delete worksheet, else an alert will display
Application.DisplayAlerts = False
PrintDlg.Delete

'Turn alerts back on
'THIS IS A NEW ADDITION, NOT SPOTTED BEFORE
Application.DisplayAlerts = True

'Select the first sheet again ("Corp" was specific to the last users solution)
Sheets("Corp").Select

End Sub[/vba]

HTH

[JimmyTheHand]

I suggest one comment to change.

[vba] 'If the dialog sheet was created..
If PrintDlg.Show Then [/vba] Actually, a dialog sheet, by default, is created with two buttons: OK and Cancel
PrintDlg.Show here behaves as a function, which returns true when OK button was clicked, and returns false when Cancel was clicked. So the comment should be:

[vba] 'If user clicked on OK button on the dialog sheet...
If PrintDlg.Show Then [/vba]

[Aussiebear]

Thanks Zack

[Aussiebear]

Okay have run into a couple of issues here. Sheet selection method is not working. To get there, enable macros/ click on deliveries and view the form with a multicombo & text data entry. This is what I'm trying to enter via the form.

The code fails on the very first sheet selection effort. What I was hoping for is that I can use the value of the cboLocation choice as the method of selecting the correct sheet and then using code very similar to that which was suggested in the other thread "writing data to subsections on the same page".

Seems I've bitten off more than I can chew, but being a fearless potential athlete, I stepped out into the darkness. Can someone please turn the light on??

Ted

[Aussiebear]

File attached for above post

[JimmyTheHand]

Could you upload the workbook? Or, at least, post the code that fails, with the line, where it fails, marked.

EDIT:
Ah, it's uploaded now. I didn't see it, sorry.

[JimmyTheHand]

Well... There are a couple of errors in your code.

On code module of userform "frmTransfer"

Variables FromLocation, FromBlock, ToLocation and ToBlock were not defined

Replace FromBlock = MecboFromBlock.Text
with FromBlock = Me.cboFromBlock.Text

On code module of userform "frmDelivery"

put an "s" into Sheet("Commodity Shed").Activate
like this: Sheets("Commodity Shed").Activate
and repeat this with all similar commands.
(Actually, I think this was the root of the problem you had.)

replace Me.txtBales , Value = ""
with Me.txtBales.Value = ""

On code module of sheet "Silage"

The sub below refers to a userform called "frmAudit", whereas no such thing exists:

[vba] Private Sub Audit_Click()
frmAudit.Show
End Sub[/vba] replace Workbook.Close
with ThisWorkbook.Close

Let's see what happens.

Jimmy

[Aussiebear]

Jimmy, Haven't got as far as the audit bit yet. Just wanted to get the sheet selection and the write data to the correct subsection (stack) on the run first then I need to somehow work out how to transfer hay from one section to another, then I was thinking of somehow creating a pivotsheet of the various storage areas (including stacks) so that as hay becomes used over the next twelve months, we can have a handy end of month audit method.

[Aussiebear]

This one's for you Bob or Jimmy, if you're game! I've done as Jimmy has suggested plus a bit more. Deliveries and transfers are mostly done I think. Can you check for errors please?

Be advised errors range from "didn't have a clue" to "the effects of the last six beers.", but hey I had a go.

Ted

[JimmyTheHand]

Ted

You can do the same thing that I would do. Use the compiler to check for errors. In VB Editor select Debug / Compile VBAProject. It will comb through all modules looking for errors, and stop if it finds one. I'm sure you can correct those errors (there are a few).

I found one thing that wasn't picked up by compiler, because it's not code error. Look at this part of the code, focusing on the red lines:
[vba]FromLocation = Me.cboFromLocation.Text
FromBlock = Me.cboFromBlock.Text
ToLocation = Me.cboToLocation.Text
ToBlock = Me.cboFromBlock.Text
BaleCount = Me.txtBaleCount.Text

'Check for From Location
If Trim(Me.cboFromLocation.Text) = "" Then
Me.cboFromLocation.SetFocus
MsgBox "Please enter a From Location"
Exit Sub
[/vba] The point is that first you create a variable to hold the value of Me.cboFromLocation.Text, then... don't use it?

Jimmy

PS:
Six beers are six beers, I understand

[Aussiebear]

I thought this was necessary to check for an error just in case the transfer button had been selected without any data being in the cboFromLocation.

Jimmy, I have almost zero knowledge with vba, so there's lots to learn. Let me crawl before you ask me to run.

Ted

[JimmyTheHand]

I thought this was necessary to check for an error just in case the transfer button had been selected without any data being in the cboFromLocation.

I'm a bit confused on this one. It could be my lack of command of English. I mean, I understand the need to check for errors, but could you do the checking after all, with the compiler? Or need detailed help with it?

Jimmy, I have almost zero knowledge with vba, so there's lots to learn. Let me crawl before you ask me to run.

I'm sorry, I'm sort of newbie here, and think of almost everyone as my better/senior. Your 330+ posts and status as VBAX Mentor (!) look quite impressive to me. I scarcely believe what you just said.

So what next?
I can debug the code if that is your wish.

Jimmy