Help with a simple menu please

[mike-dot-masse]

Hi all,

I would like to make a simple menu / macro? that will help do the
following...

When a sheet is opened, I want it to be showing a simple menu and
asking / waiting for an input -
like ...

FOR THE MAIN TASK GROUP PRESS "A"
FOR ITEMS LIST PRESS "B"
FOR ASSIGNED TIMES PRESS "C"

Excel would then wait for that input and jump to that named range

then I think when I am done with that particular area I could press
CNTRL+ HOME to go to A1 - where the "Main Menu" is ... and enter
another choice - to go to a different spot if I choose.

If someone could direct me in the right direction I would appreciate
it.

TIA

Mike

 

[JW]

Hi all,

I would like to make a simple menu / macro? that will help do the
following...

When a sheet is opened, I want it to be showing a simple menu and
asking / waiting for an input -
like ...

FOR THE MAIN TASK GROUP PRESS "A"
FOR ITEMS LIST PRESS "B"
FOR ASSIGNED TIMES PRESS "C"

Excel would then wait for that input and jump to that named range

then I think when I am done with that particular area I could press
CNTRL+ HOME to go to A1 - where the "Main Menu" is ... and enter
another choice - to go to a different spot if I choose.

If someone could direct me in the right direction I would appreciate
it.

TIA

Mike

Could an input box work? In the Open event of the workbook, place
something like below (modify to suite):
Private Sub Workbook_Open()
Dim varAnswer As String
starter:
varAnswer = InputBox("Enter A, B, or C", _
"Required Input")
If varAnswer = "" Then Exit Sub
varAnswer = UCase(varAnswer)
If Not varAnswer = "A" And Not varAnswer = "B" _
And Not varAnswer = "C" Then
MsgBox "Has to be A, B, or C", , "Error"
GoTo starter
Else
Cells(1, varAnswer).Select
End If
End Sub

 

[mike-dot-masse]

Thanks so much for the quick reply. I am new to this stuff, but will
be playing with your suggestion to see what I can do!


Mike

 

[mike-dot-masse]

I definately spoke too soon. I am most certainly lost and confused
here.

got the macro in the sheet but I cant seem to make anything happen
when the sheeet is opened. Not even an error... so I am lost here. Are
there any other suggestions .. or perhaps a basic site I could go to
and learn what I am doing wrong.

Thanks again to all

 

[JW]

I definately spoke too soon. I am most certainly lost and confused
here.

got the macro in the sheet but I cant seem to make anything happen
when the sheeet is opened. Not even an error... so I am lost here. Are
there any other suggestions .. or perhaps a basic site I could go to
and learn what I am doing wrong.

Thanks again to all

Right click the Excel logo located right beside the File menu on the
menubar. Select View Code. Paste this exact code below.
Private Sub Workbook_Open()
Dim varAnswer As String
starter:
varAnswer = InputBox("Enter A, B, or C", _
"Required Input")
If varAnswer = "" Then Exit Sub
varAnswer = UCase(varAnswer)
If Not varAnswer = "A" And Not varAnswer = "B" _
And Not varAnswer = "C" Then
MsgBox "Has to be A, B, or C", , "Error"
GoTo starter
Else
Cells(1, varAnswer).Select
End If
End Sub

Save and close the workbook. ReOpen the workbook and the code above
should run, baring that you have macros enabled.

 

[Gord Dibben]

The code is to be placed into Thisworkbook module, not a sheet module.

For instructions on placement of various types of code see Ron de Bruin's site.

http://www.rondebruin.nl/code.htm

 

[Dave D-C]

Excel would then wait for that input and jump to that named range
I don't think your example works to select the named range.
Instead ofI think you want
Range(varAnswer).select
D-C

 

[JW]

Dave, you are correct. I looked right over the fact that the OP
wanted to go to a named range. Thanks for setting me straight.

Regards
-Jeff-