Drop down list in macro

Hi everyone,

I have a question. I would like to have a macro that when activated, gives a drop down list which contains all the tabs in the workbook. The user will select one and the macro will store this selection for later use. But I dont know how to make a drop down list with all the tabs, and make it possible to select one. How do i do this?

Thanks a lot

[Jack in the UK]

errm..................

Sounds like a bit more than a nagging question to me.

[Jack in the UK]

Re thoughts,

this nees to be targetas .. thats VBA

Post in VBA section

Rdgs
----------
Jack

Where is the VBA section? i dont see any such section

[Jack in the UK]

Hi---

I cant say sorry enough YESTERDAY there was now thers not ???? as now all in one forum as should be.

Big sorryu mate i have lead you adrift my error - i did not know.

Please im sorry....

We or should i say many posters had a moan so i guess its ben adjusted i book mark to come straight here so did not know....

Any programmers out there Im Jack in the UK.. please help this guy if you can...

My mistake


Rdgs
==========
Jack

[NateO]

How's this (check box list):

Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim CurrentSheet2 As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
' Add a temporary dialog sheet
Application.ScreenUpdating = False
Set CurrentSheet2 = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
ActiveWindow.SelectedSheets.Visible = False
SheetCount = 0
' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i
' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240
' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select Appropriate Sheet"
End With
' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront
' Display the dialog box
CurrentSheet2.Select
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select
application.run("MACRO NAME")
End If
Next cb
End If
End If
' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
Application.DisplayAlerts = True
End Sub


Use:

Worksheets(cb.Caption)

to store checked worksheets.

FYI: This is a modified version of John Walkenbach's creation.

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-19 15:46 ]

Nate,

Your code looks good but one more question if you dont mind. I see that the code tests each check box, but (I should have been more clear) what i want to do is use the check box to GET the name of the tab. so, i would like only ONE check box to be ticked, and with that check box, i have a macro with does a count on the corresponding tab. So i guess what im asking is how do i assign each check box a specific tab, and if the check box is ticked, associate that tab with a variable which my count macro will work with?

Thanks much! and dont worry about the mix up Jack, I'm sure its going to take a while for us all to get used to the new board, which is nice btw

[NateO]

Anonymous,

Focus on the code near the end:

PrintDlg.CheckBoxes
If cb.Value = xlOn Then
Worksheets(cb.Caption).Select
'application.run("MACRO NAME")
End If
Next cb
End If
End If


I added the code to select the sheets that are checked. If you look at "'application.run("MACRO NAME")" line, remove the hyphen, and put your macro name in the quotes. For each checked sheet, the appropriate sheet will be selected, and your other macro will run, then the macro will run on the next checked sheet and so forth.

The only issue is that it allows the end-user to check more than one sheet. But perhaps this is acceptable....Does this help? Hope so.

Cheers,

Nate

[ This Message was edited by: NateO on 2002-02-19 15:08 ]

Well NateO,

I thought I could make it work with the code you gave me, but im getting an error saying "Object variable or with block variable not set". What does this mean? Here is my full code for reference.

Sub SheetSelector()

Dim ChooseSheet As Worksheet
Dim CurrentBook As Workbook

Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False

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

' Add a temporary dialog sheet
Application.ScreenUpdating = False
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add
ActiveWindow.SelectedSheets.Visible = False
Application.ScreenUpdating = True

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
' Skip empty sheets and hidden sheets
If CurrentSheet.Visible Then
SheetCount = SheetCount + 1
PrintDlg.CheckBoxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckBoxes(SheetCount).Text = _
CurrentSheet.Name
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 240

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max _
(68, PrintDlg.DialogFrame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Buttons("Button 2").BringToFront
PrintDlg.Buttons("Button 3").BringToFront

' Display the dialog box
CurrentSheet.Activate
Application.ScreenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckBoxes


If cb.Value = xlOn Then

'Defines selected sheet from check boxes and creates named ranges accordingly
CurrentBook = ActiveWorkbook.Name
ChooseSheet = Worksheets(cb.Caption).Value

ActiveWorkbook.Names.Add Name:="DATES", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C1,0,0,COUNTA('" & ChooseSheet & "'!C1),1)"
ActiveWorkbook.Names.Add Name:="LOANOFFICER", RefersToR1C1:= _
"=OFFSET('" & ChooseSheet & "'!R2C2,0,0,COUNTA('" & ChooseSheet & "'!C2),1)"
Range("B6").Formula = "=SUMPRODUCT(('" & CurrentBook & "'!DATES=B$5)*('" & CurrentBook & "'!LOANOFFICER=$H6))"

Range("DATA_RANGE").Select
Selection.FillRight
Range("FILLDOWN_RANGE").Select
Selection.FillDown
Range("B5").Select

End If
Next cb
End If
End If


' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete

End Sub




Thanks for all your help, its greatly appreciated!

[NateO]

I think it's because of the way you/we/Excel define variables. Here's what I would do. Isolate the macro you've done. In one 'normal' module, paste the code above (my original post). Now right-click on that module (in the project explorer), click insert, module. Paste your code into the new module. Now go back to my macro and put the name of your macro in between the quotes where it says (don't include 'sub'):

application.run("______")

I think this should work. It will run your macro on every checked sheet.

Cheers, Nate

[ This Message was edited by: NateO on 2002-02-19 18:46 ]