Using a dropdown as a menu

[MartinL]

I am trying to use a dropdown box as a menu system
so a user can jump through worksheets froma front page.

I have seen lots of snippits of code that appear to do what I want but none seem to work for me.
i.e. http://www.mrexcel.com/forum/showthread.php?t=362630&highlight=dropdown

I have a selection macro which works but need to activate it via the dropdown. I guess a chage event is needed......or am I mistaken.

Sub Selection()

Select Case Range("AF16").Value 
      Case 1
      Sheets("Asparagus").Visible = True
      Sheets("Asparagus").Select
      Sheets("Intro").Visible = False
      Case 2
      Sheets("Broccoli Cauli").Visible = True
      Sheets("Broccoli Cauli").Select
      Sheets("Intro").Visible = False
      Case 3
      Sheets("Peas Beans").Visible = True
      Sheets("Peas Beans").Select
      Sheets("Intro").Visible = False
      Case 4
      Sheets("Prep Basic Solo").Visible = True
      Sheets("Prep Basic Solo").Select
      Sheets("Intro").Visible = False
      Case 5
      Sheets("Prep Mixed Bag").Visible = True
      Sheets("Prep Mixed Bag").Select
      Sheets("Intro").Visible = False
       Case 6
      Sheets("Prep Single Packs").Visible = True
      Sheets("Prep Single Packs").Select
      Sheets("Intro").Visible = False
      Case 7
      Sheets("Speciality Veg").Visible = True
      Sheets("Speciality Veg").Select
      Sheets("Intro").Visible = False
      Case 8
      Sheets("Stirfry").Visible = True
      Sheets("Stirfry").Select
      Sheets("Intro").Visible = False
  End Select
End Sub

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "AF16" Then
    Select Case Target.Value
          Case 1
          Sheets("Asparagus").Visible = True
          Sheets("Asparagus").Select
          Sheets("Intro").Visible = False
          Case 2
          Sheets("Broccoli Cauli").Visible = True
          Sheets("Broccoli Cauli").Select
          Sheets("Intro").Visible = False
          Case 3
          Sheets("Peas Beans").Visible = True
          Sheets("Peas Beans").Select
          Sheets("Intro").Visible = False
          Case 4
          Sheets("Prep Basic Solo").Visible = True
          Sheets("Prep Basic Solo").Select
          Sheets("Intro").Visible = False
          Case 5
          Sheets("Prep Mixed Bag").Visible = True
          Sheets("Prep Mixed Bag").Select
          Sheets("Intro").Visible = False
           Case 6
          Sheets("Prep Single Packs").Visible = True
          Sheets("Prep Single Packs").Select
          Sheets("Intro").Visible = False
          Case 7
          Sheets("Speciality Veg").Visible = True
          Sheets("Speciality Veg").Select
          Sheets("Intro").Visible = False
          Case 8
          Sheets("Stirfry").Visible = True
          Sheets("Stirfry").Select
          Sheets("Intro").Visible = False
      End Select
End If
End Sub

 

[jeffreybrown]

Hi Martin,

Another possible option...

http://www.xl-logic.com/modules.php...nloaddetails&lid=218&ttitle=Userform_Examples

 

[MartinL]

Hi Peter/Jeff

Thanks for you r suggestions

Peter you answered my question and it works great

Jeff your link was beautiful.....

Martin

Martin

 

[MartinL]

Hi Jeff

You sent me a beautiful bit of code that can be used as a menu system

http://www.xl-logic.com/modules.php?...rform_Examples

If you have used this yourself do you know if it can be:
a) set up to be always active
b) positioned in a certain place on the screen (currently it is centered on the screen) without moving it.

Martin

 

[jeffreybrown]

Hi Martin,

Off the top of my head I do not have an answer for your questions.

I would just have to start a search here or maybe google.

 

[MartinL]

Thanks Jeff

Google is my buddy
just found this which works:
http://www.vbaexpress.com/kb/getarticle.php?kb_id=356

The always active won't work anyway as it will stop the user using the sheet so I'll stick to training the user how to press the big pink button to show and the red cross to hide the userform (menu).

Martin

Martin

 

[jeffreybrown]

Sounds like the best plan...the pavlov's dog experiment.