Label Caption Live Update

[ddonnahoe]

Below is a clip of my custom form from Draw 11. I want the "Total Shirts:" label to update everytime I enter a new qty in one of the sizes. Currently the code calculates the total shirts and enters it on the resulting document, but I want to add the functionality of a live Qty update.

The code is a little muddled but:

Dim sa As Integer
    Dim ma As Integer
    Dim la As Integer
    Dim xla As Integer
    Dim xxla As Integer
    Dim xxxla As Integer
    Dim yxs As Integer
    Dim ys As Integer
    Dim ym As Integer
    Dim yl As Integer
    Dim tc As Integer
            
    If ASmall1.Value = "" Then
        sa = 0
    Else
        sa = ASmall1.Value
    End If
    
    If AMedium1.Value = "" Then
        ma = 0
    Else
        ma = AMedium1.Value
    End If
    
    If ALarge1.Value = "" Then
        la = 0
    Else
        la = ALarge1.Value
    End If
    
    If AXLarge1.Value = "" Then
        xla = 0
    Else
        xla = AXLarge1.Value
    End If
    
    If ADoubleX1.Value = "" Then
        xxla = 0
    Else
        xxla = ADoubleX1.Value
    End If
    
    If YSmall1.Value = "" Then
        ys = 0
    Else
        ys = YSmall1.Value
    End If
    
    If YMedium1.Value = "" Then
        ym = 0
    Else
        ym = YMedium1.Value
    End If
    
    If YLarge1.Value = "" Then
        yl = 0
    Else
        yl = YLarge1.Value
    End If
    
    If A3X1.Value = "" Then
        xxxla = 0
    Else
        xxxla = A3X1.Value
    End If
        
    If YBaby1.Value = "" Then
        yxs = 0
    Else
        yxs = YBaby1.Value
    End If
    
    tc = sa + ma + la + xla + xxla + xxxla + yxs + ys + ym + yl

and in another area I am trying this:

If tc > 0 Then
        TCTotal.Caption = "Total Shirts: " & tc
        Else
        TCTotal.Caption = "Total Shirts: 0"
    End If

The code does not update the "tc" as I was hoping it would.

If it would make it any easier, I can provide the modules and form.

[Alex]

You need to put event handlers for "Change" events for each of the text boxes and in the body of the handlers you need to call your function which calculates the number and updates the label.

Something like this:

Option Explicit

Private Sub AMedium1_Change()
    UpdateLabel
End Sub

Private Sub ASmall1_Change()
    UpdateLabel
End Sub

'-----------------
' Other event handlers go here
'-----------------

Private Function GetValue(ByVal sValue As String) As Integer
    If sValue = "" Then
        GetValue = 0
    Else
        GetValue = Val(sValue)
    End If
End Function

Private Sub UpdateLabel()
    Dim sa As Integer
    Dim ma As Integer
    Dim la As Integer
    Dim xla As Integer
    Dim xxla As Integer
    Dim xxxla As Integer
    Dim yxs As Integer
    Dim ys As Integer
    Dim ym As Integer
    Dim yl As Integer
    Dim tc As Integer
            
    sa = GetValue(ASmall1.Value)
    ma = GetValue(AMedium1.Value)
 
    '--------------
    ' The rest of initializations go here
    '--------------
    
    tc = sa + ma + la + xla + xxla + xxxla + yxs + ys + ym + yl
    
    If tc > 0 Then
        TCTotal.Caption = "Total Shirts: " & tc
    Else
        TCTotal.Caption = "Total Shirts: 0"
    End If
End Sub

[ddonnahoe]

I'm not sure I understand the...

Option Explicit

...line of code. What is that supposed to do for this macro?

[Alex]

Originally Posted by ddonnahoe

I'm not sure I understand the...

Option Explicit

...line of code. What is that supposed to do for this macro?

Well, it's not specific for this macro. It's just a good practice to have this on the first line of your code module. It tells VBA to require explicit variable declaration. With Option Explicit specified, you can't use a variable before you declare it using DIM or similar statement.

This eliminates chances for typing error. For example:

Sub Test()
    Dim MyLongVariable As Long
    MyLongVariable = 2
    MyLongVariable = MyLogVariable + 1
    MsgBox "MyLongVariable = " & MyLongVariable
End Sub

You would think that this simple macro just show you a message "MyLongVariable = 3" but instead it will show the variable to be equal to 1. The reason is that I misspelled the third line (note the MyLogVariable instead of MyLongVariable). By default, if varible was not declared before and used, it is set to 0 or empty string and the result would be "MyLongVariable = 0 + 1" which is 1.

However if you specify Option Explicit, then the compiler will catch the error and won't even compile the code because you didn't declare MyLogVariable, which is considered a mistake.

I hope you got the point.

[Alex]

One more note, you can set VBA to automatically add "Option Explicit" to all new code modules. That's what I usually do so I don't forget to add the statement manually.

For this, go to VBA, then its Tools>Options dialog and on the Editor tab specify a checkmark next to "Require Variable Declaration".

[ddonnahoe]

That makes sense. I see things more clearly now. I'm starting to feel like Neo. LOL

[ddonnahoe]

After using Option Explicit, I came across this little snippet that wouldn't work any more and I don't fully understand it's function anyway, other than I know it calls the open dialog.

Sub ShowOpenFile()
Dim OFName As OPENFILENAME
Dim sPath As String

    OFName.lStructSize = Len(OFName)
    OFName.hwndOwner = 0&
    OFName.hInstance = 0&
    OFName.lpstrFilter = "Image Files" + Chr$(0) + "*.jpg;*.bmp;*.gif;*.jpeg;*.ai"
    OFName.lpstrFile = Space$(254)
    OFName.nMaxFile = 255
    OFName.lpstrFileTitle = Space$(254)
    OFName.nMaxFileTitle = 255
    sPath = GetBackEnd
    If sPath = vbNullString Then
        OFName.lpstrInitialDir = "c:\SharedImages\"
    Else
        sPath = Left(sPath, InStrRev(sPath, "\") - 1)
        If Len(Dir(sPath, vbDirectory)) > 0 Then
            OFName.lpstrInitialDir = sPath
        Else
            OFName.lpstrInitialDir = "c:\SharedImages\"
        End If
    End If
    OFName.lpstrTitle = "Select an image File (*.jpg), (*.bmp), (*.gif), (*.ai)"
    OFName.flags = 0

It's the line that says...

sPath = GetBackEnd

The GetBackEnd becomes undefined, but I don't know what kind of variable it should be.

[Alex]

I guess you will need to find out where you got the original code. Because that GetBackEnd seems to be meant as a function but what it is supposed to do I have no idea. Clearly there is a mistake in the code.

[shelbym]

Alex that is a wonderful tip about the option to get the Option Explicit, that will save me time.

Shelby

[ddonnahoe]

Actually, I got the code from you and shelby. It was part of the "Browse Dialog" post that I made a while back. I figured it had something to do with Windows API, or maybe I'm talking out of my butt. LOL