1

I have a userform with two text boxes to search a worksheet based on the value in either text box. The form populates other boxes based on the active cell.

How can each sub check if the other has executed?

Pseudocode:

Sub StatusTextBox_AfterUpdate()
    'Check if TransitTextBox_AfterUpdate() has run
    If yes Then End Sub
    If No Then Carry on with the rest of StatusTextBox_AfterUpdate()

This would change the value of the TransitTextBox to pull from the worksheet, and I want to prevent the TransitTextBox_AfterUpdate() from running.

As soon as the TransitTextBox is populated the AfterUpdate event runs and the two conflict. Is there a way of preventing this?

ZygD
  • 22,092
  • 39
  • 79
  • 102
Mabes
  • 25
  • 1
  • 9
  • 1
    Quick and dirty solution is to just store that is has run somewhere (hidden cell say), then check that value in the other routine. Don't forget to think about whether you need to reset it though – Tony Hopkinson Jun 10 '15 at 15:40

2 Answers2

1

SOLVED!

My Workbook_Open code wouldn't work if I had a cocktail of other excel files open. It wasn't connected to the conditional formatting you read about around this issue. I'm really not sure how this got around it because I tried the .activate and also the EVENT time delay, but neither of those would work when the glitch appeared. In this example the MSG box never opens for me to confirm the re-run was successful, however the issue has been resolved with the addition of the following code. Just substitute your code where I put the 'Comments sections

/CODE Public ImDone As Boolean

Public Sub Workbook_Open()

ImDone = True 'Run Code End Sub

Public Sub BoolCheck() If ImDone = True Then MsgBox "code has run" Else 're-run code End Sub /Code

Efiresites
  • 31
  • 4
0

Use a Global variable to allow the two subs to communicate:

Public ImDone As Boolean

Sub ShouldRunFirst()
   ImDone = True
End Sub

Sub ShouldRunSecond()
   If ImDone Then
      MsgBox "first macro has been run"
   Else
      MsgBox "first macro has not run yet"
   End If
End Sub

EDIT#1:

We need to make the communication variable visible across the board:

  • Dim it in a standard module
  • Put the declaration at the top of the module
  • Declare it a Public

It should then be "visible" to subs, and events. See some of the answers Here

Community
  • 1
  • 1
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • Gary's Student thanks for the reply. Will a Global variable work for userform events as well? – Mabes Jun 11 '15 at 11:36