Data Validation Event

[bforster]

Hello.

I am new to Excel VBA and am making progress but am having a tough time with getting a VBA event to trigger when selecting different Cases in a dropdown

Below is some simple code I have written just to find a way to get the code right.

I have a drop down field (AE48) and the validated cells are W77:W79

Ultimately I want to write more sophisticated code when the drop down (data validation) cell is changed but first want to confirm I am coding right to have an event trigger when I change the drop down

In advance, thank you


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W77:W79")) Is Nothing Then
Cancel = True
ActiveSheet.Range("AE48").Value = ActiveCell.Value
If Sheets("Sheet1").Range("AE48").Value - "Floating" Then
Sheets("Sheet1").Range("A1").Value = 6
Else
Sheets("Sheet1").Range("A1").Value = 5
End If
End Sub

 

[Scott T]

The change event should look at the cell changing.

This will run the code when someone changed the value in AE48. the code you have would only run the code when W77:W79 change

If Not Intersect(Target, Range("AE48")) Is Nothing Then

 

[bforster]

Thanks for the quick reply Scott T. but made change and still nothing.

AE48 is a drop down data validation cell with Floating, Fixed and Do Not Know as the options.

You willingness to stick with me as I work this out is appreciated. P.S. the code is in the Sheet1 coding section

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AE48")) Is Nothing Then
Cancel = True
ActiveSheet.Range("AE48").Value = ActiveCell.Value
If Sheets("Sheet1").Range("AE48").Value - "Floating" Then
Sheets("Sheet1").Range("A1").Value = 6
Else
Sheets("Sheet1").Range("A1").Value = 5
End If
End Sub

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("AE48")) Is Nothing Then
      If Target.Value = "Floating" Then
         Range("A1").Value = 6
      Else
         Range("A1").Value = 5
      End If
   End If
End Sub

 

[bforster]

Guys.

What am I doing wrong. This is the last piece of my puzzle.

Have the following code in Sheet1 section and AE48 is data validation field with the 3 options.

Nothing is happening. Check all cell references.

Does "Floating" have to have "" around it?? Anything else you would suggest?

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("AE48")) Is Nothing Then
      If Target.Value = "Floating" Then
         Range("A1").Value = 6
      Else
         Range("A1").Value = 5
      End If
   End If
End Sub

 

[Fluff]

When posting code please use code tags, the # icon in the reply window.

Add the word stop as the first line like

Private Sub Worksheet_Change(ByVal Target As Range)
   Stop
   If Not Intersect(Target, Range("AE48")) Is Nothing Then
      If Target.Value = "Floating" Then
         Range("A1").Value = 6
      Else
         Range("A1").Value = 5
      End If
   End If
End Sub

and then change the value in AE48.
Does the code window open up with Stop highlighted in yellow?

 

[bforster]

Still nothing...I have tried everything.

1. Is the code supposed to be in the Sheet or Workbook code page
2. The cell changing (AE48) is a data validation drop down
3. Do I need to reference the Sheet (Sheets("Sheet1").Range("A1") in the if event
4. Seems that the event is not even triggering

Thanks again this is my last step in the spreadsheet I am creating....VBA is a blast but this is frustrating.

 

[Fluff]

The code needs to go in the sheet module, for the sheet containing the dropdown.

 

[bforster]

Thanks to everyone's help I solved the puzzle...thanks

One more question

I have multiple data validation drop downs and I want different events to happen but I get an ambiguous error when I use this more than once Private Sub Worksheet_Change(ByVal Target As Range)

Thanks

 

[Fluff]

You need to do it like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, Range("AE48")) Is Nothing Then
      If Target.Value = "Floating" Then
         Range("A1").Value = 6
      Else
         Range("A1").Value = 5
      End If
   ElseIf Intersect(Target, Range("????")) Is Nothing Then
      'do something
   End If
End Sub