Drop down list in macro

[Guest]

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

 

[Guest]

Ok, sorry for the incompetence on my part, but yet another question. I tried what you suggested, but now I'm having a problem with the following line:

ChooseSheet = Worksheets(cb.Caption).Value

which is in MY macro that i called from your code. How do i make a reference to something from your code from my macro? I have to use the sheet name in my code as thats what it does. Hopefully this will be my last question Thanks very much for the help.

 

[NateO]

Anon,

No problemo. It seems to me you should be in the sheet you're adjusting; accordingly, I would change (in your macro):

ChooseSheet = Worksheets(cb.Caption).Value

to

ChooseSheet = activesheet.name

Me thinks this ought to work.

Cheers,

Nate

 

[Guest]

Finally!! it works great, thanks very much

 

[NateO]

Nice! Glad to hear it.

Happy hunting. Cheers, Nate

 

[BMack21]

Hello,

I modified this so that it will delete unchecked sheets, but when I run the macro, excel gives me an "Are you sure....you want to delete" message. How can I get rid of that message so it automatically deletes them without the prompt?

CurrentSheet2.Select
    Application.ScreenUpdating = True
    If SheetCount <> 0 Then
        If PrintDlg.Show Then
            For Each cb In PrintDlg.CheckBoxes
                If cb.Value = xlOff Then
                Worksheets(cb.Caption).Delete
                'Application.Run ("MAC
                End If
            Next cb
        End If
    End If

Thanks