Problem updating a worksheet after selecting from a drop down list

[spurs]

Every time an entry is made on the worksheet "Geometry" the macro Every_Update runs

This is how the command is written in the Workbook_Open() event

Private Sub Workbook_Open()
Worksheets("Geometry").OnEntry = "Every_Update"
End Sub

This has worked well for years as Every_Update runs with every worksheet entry

I am trying to add a drop down selection onto the worksheet Geometry. There are 4 selections in the drop down list, A, B, C and D

When selecting from the drop down list, say "B" the macro Every_Update does not run. I suspect this is because when selecting from the drop down list, the entry key is not pressed on the computer keyboard and hence "Every_Update" does not run.
It is important to run Every_Update because the options A to D are intended to change formatting and formula entries in 3 or 4 cells in the worksheet Geometry. The formatting does not happen after the drop down list item is selected because enter is not pressed.

I want Every Update to run once the drop down list item is selected.

To do this in the worksheet module Geometry, i added the following code

Private Sub Worksheet_Change(ByVal Target As Range)
Every_Update
End Sub


This has proven to be a problem because it seems to get into an endless loop of updates
I assume that Every update runs as a result of the sub.workbook().open command and it also runs as a result of the worksheet module. It becomes an endless loop

I have two questions:
1) Is the best way to resolve this by keeping the sub workbook.open() approach and remove the worksheet coding? If i do this, how can i get the drop down list once selected to do an entry to run every update

2) Is it better to remove the sub workbook.open() function and leave the worksheet event ?

or is there another solution to prevent an endless loop

 

[sykes]

Need to get the code in your "Every_Update" event, into the worksheet_change event.
Please post Every_Update code.
In which cell(s) is/are your drop-down list(s)?
Name of worksheet?

 

[spurs]

Drop down list box is in a merged cell worksheet Geometry cell C:15-I:15

Every_update is extremely long and has many calls off of it
I am appending it here for ease


Option Private Module

Sub Every_Update()

'************************************************************************************************************
' This macro is intended to run with every update of the worksheet that changes geometry or plotting
'************************************************************************************************************

On Error Resume Next

' code removed here that has nothing to do with geometry worksheet

'Decide what other subroutines to call based on the type of gear being calculated


Call x_axis1
Call x_axis2
'Call x_axis3
Call Diagnostics


End Sub



In the above routine the macros
x_axis1
x_axis2
x_axis3

will make changes to the geometry worksheet
before adding the drop down list onto the sheet, Every Update ran in about half a second but x_axis 1,2 and 3 were not part of the code

in testing it, if i disable the every update in the excel object sheet, but leave every_update otherwise unchanged including x_axis 1, 2 and 3, , the macro runs in about half a second
the problem is how do i get the macro to run once not only by a worksheet entry but also through the drop down list selection

 

[sykes]

Can't you just do what you suggested - in the worksheet_change event, but test for the drop-down merged cell, and if that's not the changed cell then come out of the procedure?
That way, it shouldn't keep firing Every_Update, each time any other cell changes:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("C15")) Is Nothing Then Exit Sub
Every_Update
   
End Sub

... I'm guessing that your merged cell's address is C15, but of course, change to suit.

 

[spurs]

Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("C15")) Is Nothing Then Exit Sub Every_Update End Sub

That worked
thank you so much

 

[sykes]

Pleasure. Glad it worked for you, and thanks for the feedback.