Thread: Alternative to buttons needed

Why not create a user form that pops up with multiple command buttons or links?

Use a data validation list with a worksheet change event linked to that cell.

You mean like a Navigation Console type of thing? That could work, but I'm thinking maybe instead using Data Validation, then using a Select Case statement of some sort in the Worksheet_Activate.

Bob,

Looks like our posts crossed. You and I are on the same wavelength. My Worksheet_Activate macro currently looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet:    Set ws = Sheets("View1Pivot")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Chart-View 1")

Application.ScreenUpdating = False

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Address = "$E$1" Then
    ws.Unprotect
    ws.AutoFilterMode = False
    ws.Range("N:N").AutoFilter Field:=1, Criteria1:=ws2.Range("E1").Text
    ws.Protect
End If
Application.ScreenUpdating = True
End Sub

E1 is where they select their region.
If I create a Data Validation in N1, with about 8 different options, how would I change this macro to handle all of that?

Okay, I've tried on my own, but it's not working. I've set up my Data Validation list in N1, but when I select, say "Go to View 1" (I'm on View 1) the message "There is nothing else" fires up. That message fires up no matter what I select. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet:    Set ws = Sheets("View1Pivot")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Chart-View 1")

Application.ScreenUpdating = False

If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

    If Target.Address = "$E$1" Then
    ws.Unprotect
    ws.AutoFilterMode = False
    ws.Range("N:N").AutoFilter Field:=1, Criteria1:=ws2.Range("E1").Text
    ws.Protect
End If
    If Target.Address = "$N$1" Then
    ws.Unprotect
    ws.AutoFilterMode = False
      Select Case LCase(Target.Value)
     Case "View the Details"
        Details
     Case "View Positions for this Area"
        ActivePosns
     Case "Go to View 1"
        MsgBox "You're currently looking at View 1"
     Case "Go to View 2"
        View2
     Case "Go to View 3"
        View3
    Case "Go to the Transferable PACs"
        MsgBox "we'll work out the code"

     Case Else
        MsgBox "There is nothing else!"
  End Select
End If
Application.ScreenUpdating = True
End Sub

Hmmm...,

SNB, I see what you mean. That's what I get from copying snippets of code from here and there.

Going with JasLake's suggestion, using

Select Case Target.Value

does the trick. Alternatively, putting

Option Compare Text

at the top of the code module works also. But I think I'll just go with JasLake's suggestion.

Thanks for all the help on this one!

John