Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Drop down list in macro

  1. #1
    Guest

    Default

    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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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


  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Re thoughts,

    this nees to be targetas .. thats VBA

    Post in VBA section

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


  4. #4
    Guest

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  7. #7
    Guest

    Default

    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

  8. #8
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  9. #9
    Guest

    Default

    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!

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •