Worksheet_Change not working?

J

Janet_B

Board Regular
Joined
Dec 6, 2004
Messages
72
Hi,

I'm using Office 97 and I can't get the code to run below.

$B$3 is a validation list and if I double click it and select another cell it works but not when I select a value for the drop down.

Also, under Options I have calculations to run automatically.

Thanks JB

Code: 
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Application.ScreenUpdating = False
    If Target.Address = "$B$3" Then
    	Range("AG18:AJ24").Copy
    	Range("F18").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    	Application.CutCopyMode = False
    End If
    Application.EnableEvents = True
 End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
Excel 97 will not fire the change event from a dropdown unless the list is actually typed into the source textbox. An alternative is to use the calculation event.
 
Upvote 0
There was a thread regarding this recently, but I can't find it now. The suggestion was to use a UserForm with a Listbox linked to a cell. This will also trigger the change event and is not a messy as the calculate event.

HTH

lenze
 
Upvote 0
In that case, you could simply place a forms textbox on your worksheet, set the linkedcell property to your cell's address containing the dropdown, move the textbox out of view or set it's visible property to false, and then capture the dropdown selection change in the textbox's change event. I have used this method with success when trapping DDE updates that cannot be trapped using the SetLinkOnData method. Thanks for the reminder Lenze. :)

Excel97DropdownLimitation.zip
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
VBA Speed up Copy and paste
Replies
3
Views
363
skillilea
S
P
  • Solved
Counter in For Each loop
Replies
2
Views
186
Possum17
P
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
167
pennbowl
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
299
Azamamir
A
J
  • Question
another way to speed the macro .
Replies
3
Views
501
Georgiboy
Georgiboy

Forum statistics

Threads
1,214,653
Messages
6,120,748
Members
448,989
Latest member
mariah3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top