excelvba6

UserForm_Initialize not processing as expected, etc


Apologies if I've not posted this correctly. I don't yet know how to put vba coding into its own window. I’ve been relearning to use excel and vba try to automate an excel monthly meal plan for a seniors’ home in which I live. After several months, I’ve accomplished a lot. That said, I’ve been exploring how I might optimize my coding, reduce redundant variable, and restructure the file by expanding the versatility of some subs. To that end, I’ve integrated Debug.Print to help me gain understanding as to what is happening. What an eye-opener!!!!

Just within my first few subs I saw things happening I needed to understand to better design my subs and functions. In truth, I have yet to apply functions, but their structure seems to be more complicated for my aging brain grasp. Two important things at this point I hope can be explained as they could vastly improve the entire structure of my file. I’ve embedded comments in my coding to try to limit the length of my question and provide context

Questions:

  1. Before cleaning everything up for the sake of preparing this for your guidance, the debug.print yielded something along the lines of Build_ddnUnitNo(D112) where those sub were just …. Sub Subname() …. That being the case, how might I better take advantage of what was/is going on here?
  2. Should a global variable set in Worksheet coding not work for the entire project? (I hope I’m using the correct terminology to be understood.) [Edit: I'll leave this in rather than remove it as I've already gotten a workable answer from rotabator, even after I solved the issue myself.]
  3. I’ve tried turning a sub into a function but run into problems in knowing just how to set up the variable when their options are so vast. And while I’m pretty adept at resolving whatever my issues are, but this one, for all its efficiencies, could be just to time consuming than just creating the workarounds. At least for me.
    Thank you for your time and patience.
Option Explicit
Public WksFlag As Boolean
Public WksRow As Integer, LdRow As Integer

Sub Worksheet_BeforeDoubleClick(ByVal WksUn As Range, WksFlag As Boolean)
    Subroutine = "SUB: Wks_B4DblClick"
    Debug.Print "---> Start " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
    If WksUn.Column = 1 Then ' WksUn for Worksheet UnitNo (D112)
        WksRow = WksUn.Row ' These dims should be global/public 
        LdRow = WksRow – 4 ' LdRow for ListDiners in User Form
        formMealPlan.txtcRow.Value = LdRow
        Load frmMealPlan
        frmMealPlan.Show vbModeless
    End If ' WksRow, LdRow, WksUn don’t seem to immediately Public
    ' Even if I also declare them as well in formMealPlan. (See included printout) 
    Debug.Print vbNewLine & "<--- End " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
End Sub

' formMealPlan (userform) Code
Public LdRow As Integer, WksRow As Integer

Sub UserForm_Initialize()
    Subroutine = "SUB: UserForm_Initialize"
    Debug.Print "---> Start " & Subroutine & "()"
    Debug.Print "txtcRow(" & txtcRow & ")"    <<<< This is here to display my point
    Exit Sub ' I'll move this below Build_ddnUnitNo for 2nd run
    Build_ddnUnitNo ' Populates the Unit Number drop down list
End Sub

The Debug Printout

1st  Debug.Print  run

 ---> Start SUB: Wks_B4DblClick(D112, False)
 ---> Start SUB: UserForm_Initialize()
 txtcRow()
 ---> Start Sub: txtcRow_Change()
 <--- End Sub: txtcRow_Change()      <<< This appears Build_ddnUnitNo sub (see below)
 <--- End SUB: Wks_B4DblClick(D112, False)

2nd  Debug.Print  run 

 ---> Start SUB: Wks_B4DblClick(D112, False)
 ---> Start SUB: UserForm_Initialize()
 txtcRow()      <<< If this weren’t blank, I could use it immediately rather than 
    <<< have to force set it.
 ---> Start SUB: Build_ddnUnitNo()
 <--- End SUB: Build_ddnUnitNo()
 ---> Start SUB: txtcRow_Change ()
 <--- End Sub: txtcRow_Change()      <<< I can’t seem to move this up. [Edited July 28th, 9:30 EST: Nothing can be done to alter the order of this since it's responding to what's happening inside a form object. But the answer provided from rotabator contributed to help making it a non-issue. 

Solution

  • Didn't get the whole idea, but can propose to fix some details.

    Create a new module to declare global variables. They can't be declared in worksheet's private code.

    Option Explicit
    Public WksFlag As Boolean, Subroutine As String
    Public WksRow As Integer, LdRow As Integer
    

    Your worksheet private code

    Option Explicit
    ' variables declared here are not global,
    ' they are just (public) members of a worksheet object
    ' They, if declared as public, can be accessed outside
    ' with syntax "Worksheetcodename.WksFlag"
    ' where "Worksheetcodename" is the name of the worksheet object
    ' Public WksFlag As Boolean, Subroutine As String  
    ' Public WksRow As Integer, LdRow As Integer
    
    Sub Worksheet_BeforeDoubleClick(ByVal WksUn As Range, WksFlag As Boolean)
      Subroutine = "SUB: Wks_B4DblClick"
      Debug.Print "---> Start " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
      If WksUn.Column = 1 Then
        WksRow = WksUn.Row ' accesses global variable
        LdRow = WksRow - 4 ' accesses global variables
        ' formMealPlan.txtcRow.Value = LdRow ' can't be accessed here
        ' since txtcRow is the private member of formMealPlan
        Load formMealPlan
        ' any form's public variable can be accessed after form loads
        formMealPlan.DemoVar = 5 ' demo
        formMealPlan.Show vbModeless
      End If
      Debug.Print vbNewLine & "<--- End " & Subroutine & "(" & WksUn & ", " & WksFlag & ")"
    End Sub
    

    formMealPlan (userform) Code

    Option Explicit
    ' Public LdRow As Integer, WksRow As Integer ' evidently it's extra declaration
    Public DemoVar as Integer
    
    Sub UserForm_Initialize()
      txtcRow.Value = LdRow ' accesses global variable
      Subroutine = "SUB: UserForm_Initialize"
      Debug.Print "---> Start " & Subroutine & "()"
      Debug.Print "txtcRow(" & txtcRow & ")" '   <<<< This is here to display my point
      ' I'll uncomment Build_ddnUnitNo for 2nd run
      ' Build_ddnUnitNo ' Populates the Unit Number drop down list
    End Sub
    

    enter image description here