Synchonizing Worksheet Change Event when cell is changed from user form

[bisel]

Hello All,

I am using a worksheet change event to run a macro when a specific range of cells change in the sheet. All is well when one makes the change while the worksheet is in focus ... i.e., the user is making changes directly on the sheet. The change event updates the y-axis of three charts setting all three with the same minimum and maximum values.

But, I also have a user form with text boxes. The user can enter a value in the text box, which then updates the worksheet. However, using this method to update the sheet does not trigger the change event on the sheet ... unless one were to select a cell on the worksheet and then the act of selecting the cell triggers the change event.

My question, is there a way for a change event to function when the worksheet does not have focus? If not, what do you think is the best way to accomplish this? Perhaps put a change event in the user form to run the macro that would update the worksheet in question? But that would take some time putting the change on multiple text boxes.

Any thoughts?

Thanks,

Steve

 

[Fluff]

Are you talking about a Change event or a SelectionChange event?
Because a change event works for me from a userform.

 

[bisel]

I was able to answer my own question with a simple solution ...

On the target worksheet, I have a macro that runs upon sheet activation.

I added the following code as a change event on the user form ...

Application.Run ("finance_charts_update")  ' Run the macro to update the Finance Summary Charts

where, "finance_charts_update", is a public macro with this code ...

Sub finance_charts_update()

Dim currentshtname As String

Application.ScreenUpdating = False

currentshtname = activesheet.Name ' get current sheet name
Sheet7.unprotect ' unprotect the Financial Summary Charts sheet
Sheet7.Select ' When selecting this sheet, the activate sheet macro will run which resets the three charts y-axes
Sheets(currentshtname).Select ' after selecting and running the activate sheet macro, return focus to the current sheet.

Application.ScreenUpdating = True

End Sub

Once the target sheet is activated / selected, the macro runs to update the three charts in question. Once complete the focus returns to the current sheet that was selected before running. Seems to work fine so far. More testing in order.

Steve

 

[Fluff]

Glad you got it sorted & thanks for the feedback