I Dont understand how to create a prompt

**smraines** · 12-13-2009, 02:48 PM

I am trying to create a macro that shows a report. I have 27 worksheets and would like to just be able to run a macro that takes me to the sheet I want to go. Im not very good with Visual Basic and I dont really know how to code this. Here is just a quick sample of what Im looking at in VB.

Sub Show_Report()
'
' Show_Report Macro
' Macro recorded 12/13/2009 by Mindy Raines
'
' Keyboard Shortcut: Ctrl+a
'
    Sheets("All").Select
    Range("A1").Select
    Sheets("Northwest").Select
    Range("A1").Select
    Sheets("Southeast").Select
    Range("A1").Select
    Sheets("Midwest").Select
    Range("A1").Select
    Sheets("Southwest").Select
    Range("A1").Select
End Sub

I would like to have a prompt popup so that I can type in which sheet I would like to see. Is this a possibility in excel?

Thanks,
Steve

**jeffreybrown** · 12-13-2009, 03:01 PM

Well if you want to see how a VBA rookie would do it...

I'm sure we will find a better way, but this way may save somebody from having to type the sheet name exactly. Change sheet names to match your desires.

Sub Show_Report()

Dim MySheet As String

    MySheet = InputBox("Type a number to view one of three different calculators" _
    & vbNewLine & _
    vbNewLine & _
    "1 = Sheet1" & _
    vbNewLine & _
    "2 = Sheet2" & _
    vbNewLine & _
    "3 = Sheet3")
    
Select Case MySheet
Case 1
Sheets("Sheet1").Activate
Case 2
Sheets("Sheet2").Activate
Case 3
Sheets("Sheet3").Activate
End Select
End Sub

**teylyn** · 12-13-2009, 03:53 PM

Hi smraines,

welcome to the forum.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

I've added the tags for you this time, but please remember to use them in the future.

**davesexcel** · 12-13-2009, 03:59 PM

You could just right click on the sheet arrows at the bottom left corner of the workbook and pick what sheet to go to

**smraines** · 12-13-2009, 06:06 PM

Okay here is my problem. Can someone please take a look at this. I need to have a popup asking me what report I want to see when I run my macro. The same with when I want to see a chart. My macros are working, but I cannot combine them. When I get my popup box and I type in "Midwest", it will send me to the "All" report. I need to be able to search the charts off of one macro and the same with the reports. I also am trying to figure out how to setup an error message if someone misspells the report so that it will tell them how they misspelled it.

Any help would be greatly appreciated.

Also, sorry for not putting code tags. If someone will tell me how to do that I will make sure to use them from now on.

Thanks,
Steve

**davesexcel** · 12-13-2009, 08:06 PM

This is a little bit more than what you asked for, Hit Ctrl & A to open the UserForm, select the sheet you want to go to.

**royUK** · 12-14-2009, 03:27 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.