User Guided Button???

Hi guys,

I have 100 worksheets all with different names and a main menu. From the main menu I want to be able to press a button that opens an input box in which I can type the name of the worksheet I want to go to and then a macro will take me to that worksheet.

I've been experimenting and have the following code in VB. It brings up an input box but then whenever I enter anything and click OK, an error message appears. Could anyone give me a hand???

Thanks

Mark

Heres my code:-

Sub RoundToZero()
Worksheets("Main Page").Activate
On Error GoTo PressedCancel
Set Pub = Application.inputbox( _
prompt:="Enter your EKR Pubn Code", _
Type:=0)
Worksheets("Pub").Activate

Exit Sub

PressedCancel:
Resume
End Sub

Hello"

The statement
Worksheets("Pub").Activate
tries to activate a worksheet called "Pub", which most likely doesn't exist

Just omit the quotation marks:
Worksheets(Pub).Activate

Herbert

"locutus243" wrote:

>
> Hi guys,
>
> I have 100 worksheets all with different names and a main menu. From
> the main menu I want to be able to press a button that opens an input
> box in which I can type the name of the worksheet I want to go to and
> then a macro will take me to that worksheet.
>
> I've been experimenting and have the following code in VB. It brings up
> an input box but then whenever I enter anything and click OK, an error
> message appears. Could anyone give me a hand???
>
> Thanks
>
> Mark
>
> Heres my code:-
>
> Sub RoundToZero()
> Worksheets("Main Page").Activate
> On Error GoTo PressedCancel
> Set Pub = Application.inputbox( _
> prompt:="Enter your EKR Pubn Code", _
> Type:=0)
> Worksheets("Pub").Activate
>
> Exit Sub
>
> PressedCancel:
> Resume
> End Sub
>
>
> --
> locutus243
> ------------------------------------------------------------------------
> locutus243's Profile: http://www.excelforum.com/member.php...o&userid=12862
> View this thread: http://www.excelforum.com/showthread...hreadid=483423
>
>

Hey,

Thanks for taking a stab at trying to help me, I really appreciate it.

I took out the quotation marks but when I enter my worksheet name in the inputbox it still throws an error message at me.

I have 100 worksheets each title is a 2 or three digit number (i.e. 351, 54). I want to be able to type my number in and then it'll go to that worksheet. I don't understand why its not working, the code seems alright to me (until the error msg appears). But I have very little knowledge of VB..

Thanks

Mark

See response in excel.misc

--

HTH

RP
(remove nothere from the email address if mailing direct)


"locutus243" <locutus243.1y86ga_1131532502.4764@excelforum-nospam.com> wrote
in message news:locutus243.1y86ga_1131532502.4764@excelforum-nospam.com...
>
> Hey,
>
> Thanks for taking a stab at trying to help me, I really appreciate it.
>
>
> I took out the quotation marks but when I enter my worksheet name in
> the inputbox it still throws an error message at me.
>
> I have 100 worksheets each title is a 2 or three digit number (i.e.
> 351, 54). I want to be able to type my number in and then it'll go to
> that worksheet. I don't understand why its not working, the code seems
> alright to me (until the error msg appears). But I have very little
> knowledge of VB..
>
> Thanks
>
> Mark
>
>
> --
> locutus243
> ------------------------------------------------------------------------
> locutus243's Profile:
http://www.excelforum.com/member.php...o&userid=12862
> View this thread: http://www.excelforum.com/showthread...hreadid=483423
>