VBA Data Validation List Box

[Pinaceous]

Hi All,

I understand that I can have a Data Validation List Box where it runs a sub such as:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Select Case Range("B2")
        
            Case "October": Test
            Case "November": Test
            Case "December": Test
                        
        End Select
    End If
    End Sub

But I'm also noticing that the sub here listed as the "Test" macro has to be placed within Microsoft Excel Objects in the Sheet or ThisWorkbook to function.

Is there anyway this Private Sub can be rewritten to say something like:

Sub Worksheet_List()

When ...

Worksheets(1).Cell ("B2")Selected

Then ...

    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Select Case Range("B2")
        
            Case "October": Test
            Case "November": Test
            Case "December": Test
                        
        End Select
    End If
    End Sub

Please let me know.

Thank you,
pinaceous

 

[Jeffrey Mahoney]

Look in VBA above the code window for any sheet. If you select Worksheet from the pulldown on the left, you can now see all the options for actions that occur when you do things on your sheet. SelectionChange is a trigger that gets called when you cursor around or click on a cell. Now, how do you test for the cell you need??

The code below tests which cell got selected and reacts. Be careful, because even if you select a range of cells and "B2" is one of them, it will still react, that's why I added the code to not react when there is more than one cell selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Range
  
  If Target.Count > 1 Then Exit Sub
  Set i = Intersect(Target, Range("B2"))
  If Not i Is Nothing Then
    'Go do something
  End If
  
End Sub

 

[Pinaceous]

Hi Jeffrey,

Okay, thank you for improving upon the code.

But is there any way I can take the private out of the code from the objects sheets and place it into a regular module?

Thanks,
pinaceous

 

[Pinaceous]

Okay Jeffrey,

I can deal with it being a private sub but how where would I put several macros together in running the "test" in post #1?

How would they be organized?

For example,

If "Test" is structured like:

Sub Test ()

Call Open

Call DoSomething

Call DoSomethingElse

Call Close

End Sub

Where would I put all of this?

In the same worksheet?

Thanks,
pinaceous