View original (French)

A private sub within a sub

Solved
hcetat Posted messages 7 Registration date   Status Member Last intervention   -  
hcetat Posted messages 7 Registration date   Status Member Last intervention   -
Hello,

Thank you for your article titled "VBA - Calling a macro within another macro," which is very well written and clear.

I am trying to adapt the code you provided as an example to the case I need to resolve.
I am not an expert in VBA, and there is something I am missing.
Here is the example from your article:

Private Sub Worksheet_Change(ByVal Target As Range)
'If the cell affected by the change does not have the address A1 => exit
If Target.Address <> "$A$1" Then Exit Sub
'If the cell (thus A1) is > 10 then place "Not bad!" in B1
If Target.Value > 10 Then Target.Offset(0, 1) = "Not bad!" Else Target.Offset(0, 1) = "Not great!"
End Sub

Sub MyMacro()
Dim myRange As Range
Set myRange = Sheets("Sheet1").Range("A1")
Call Worksheet_Change(myRange)
End Sub

If I create a macro without changing anything and run it, Excel immediately places "Not great!" in B1 and then, no matter what I enter in A1, B1 does not change anymore.
I might be wrong, but I was expecting B1 to change based on the value of A1.

What have I not understood?

Additionally, here is what I wish to do.
A user must make a choice from a validation list.
Once this is done, the program should continue to run.

Can you tell me how to continue the program once the choice is made?

Thank you in advance for your response.

Best regards,

Henri

Configuration: Windows / Chrome 48.0.2564.116

4 answers

  1.
    pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    Hello,

    Based on the WaitForUserInput function by Chip Pearson.
    You need:
    In a standard module the code: 
    Option Explicit Public Changee As Boolean Sub Test() Dim B As Boolean MsgBox "You must make your choice in cell A1 Sheet1" 'here we wait B = User_Wait(Worksheets(1).Range("A1"), 10) 'after waiting If B = True Then MsgBox "cell A1 has changed, it contains: " & Range("A1").Value Else MsgBox "the cell has not changed" End If End Sub Public Function User_Wait(Changing_Cell As Range, Max_Duration As Integer) As Boolean Dim Start As Double Changee = False Start = Now Do DoEvents If Changee = True Then User_Wait = True Exit Function End If Loop While Now - Start < TimeSerial(0, 0, Max_Duration) User_Wait = False End Function


    and in the module of Sheet1 (for the example): 
    Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Changee = True End Sub


    --
    Before, I could never finish my sentences... but now I
    1
  2. hcetat Posted messages 7 Registration date   Status Member Last intervention  
     
    Thank you for the response.

    My issue is slightly different as I have to wait, indefinitely if necessary, for the user to make their choice from the list below: 
     
    With Selection.Validation
     .Delete
     .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
     xlBetween, Formula1:="BIACCDF ,BIACUSD ,RAWUSD ,TMBCNF ,TMBUSD "
     .IgnoreBlank = True
     .InCellDropdown = True
     .InputTitle = "Your accounts"
     .ErrorTitle = ""
     .InputMessage = "Choose an account by clicking the arrow"
     .ErrorMessage = ""
     .ShowInput = True
     .ShowError = True
     End With


    Once their choice is made, the macro must continue to execute.

    According to your article, it can be called with a command like 


    Call Module1.ListeDesPaiementsSuite


    but my question is: how to automatically start the continuation (call...) when cell E1, which contains the choice, is filled?

    Thank you for your patience

    Henri
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      It's very similar to what I was proposing.
      In the Feuil1 module 
      Private Sub Worksheet_Change(ByVal Target As Range) If Target = Cell_Changeante Then Changee = True End Sub


      And in a standard module: 
      Option Explicit Public Changee As Boolean, Cell_Changeante As Range Sub test() Dim B As Boolean Worksheets("Feuil1").Range("E1").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="BIACCDF ,BIACUSD ,RAWUSD ,TMBCNF ,TMBUSD " .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Your accounts" .ErrorTitle = "" .InputMessage = "Choose an account by clicking on the arrow" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set Cell_Changeante = Worksheets("Feuil1").Range("E1") B = User_Waiting MsgBox "value changed, proceeding" End Sub Public Function User_Waiting() As Boolean Dim Start As Double Changee = False Start = Now Do DoEvents If Changee = True Then User_Waiting = True Exit Function End If Loop User_Waiting = False End Function
      0
  3. hcetat Posted messages 7 Registration date   Status Member Last intervention  
     
    Thank you very much, it’s starting to smell like a stable! :-)

    I really feel that we are very close to hitting the jackpot, but there’s still a small detail to sort out.

    The sheet on which the treatment is applied is initially opened with 


     On Error Resume Next
     ChDrive "X:"
     If Error Then
     ChDrive "C:"
     ChDir "C:\WinBooks\DataExcel"
     Else
     ChDir "X:\DataExcel"
     End If

     nom = Application.GetOpenFilename("File name,*.XLS")
     If nom <> False Then
     Workbooks.Open nom
     Else
     Exit Sub
     End If
     On Error GoTo 0



    Which means I cannot place the following in Sheet1 in advance 


    Private Sub Worksheet_Change(ByVal Target As Range)
     If Target = Cell_Changeante Then Changee = True
    End Sub


    So what should I do then?

    Thank you again for your responsiveness.
    0
    1. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
        
      Option Explicit Public Cell_Changeante As Range Sub test() Dim B As Boolean, Valeur_Initiale As String Worksheets("Feuil1").Range("E1").Select With Selection.Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="BIACCDF ,BIACUSD ,RAWUSD ,TMBCNF ,TMBUSD " .IgnoreBlank = True .InCellDropdown = True .InputTitle = "Your accounts" .ErrorTitle = "" .InputMessage = "Choose an account by clicking on the arrow" .ErrorMessage = "" .ShowInput = True .ShowError = True End With Set Cell_Changeante = Worksheets("Feuil1").Range("E1") Valeur_Initiale = CStr(Cell_Changeante.Value) B = Wait_User(Valeur_Initiale) MsgBox "value changed, continue" End Sub Public Function Wait_User(Value As String) As Boolean Do DoEvents If Cell_Changeante.Value <> Value Then Wait_User = True Exit Function End If Loop Wait_User = False End Function
      0
  4. hcetat Posted messages 7 Registration date   Status Member Last intervention  
     
    Hallelujah! It works!
    I replaced MsgBox "value changed, continuing" with the Call and bingo!

    Congratulations and thank you!

    I should definitely spend a few days taking courses, I think I would benefit immensely!

    Thanks again!

    Henri
    0