Global variable reset between subroutine calls 1

[rabley]

I’m completely stuck on this one! I’m programming an entry form for entering product information into an Excel spreadsheet. Until recently, my “Undo” button worked just fine. Now, it’s stopped working and I haven’t specifically been testing it on every save, so I don’t know which changes I made could be affecting it. The one thing I know I’ve done is switch from vbmodal to vbmodeless, but I don’t see how that could affect this.

Basically, the form allows the user to enter several products with one click of “Submit”, such as five different colors of the same product or three different sizes of the same envelope. I have a public variable “intProductCounter” that increments with each iteration of colors or envelope sizes. The “Submit” button that takes all the form entry data and populates the spreadsheet, then offers a Msgbox that says, “You have added [intProductCounter] products to the spreadsheet.”

This message is still working properly, so I know intProductCounter is still working at that time. However, as soon as I click away that MsgBox, it appears that intProductCounter is resetting to 0. This is a problem, because my “Undo” button uses that count to know how many rows to delete.


For testing purposes, I added a Command Button that solely creates a msgbox displaying intProductCounter. When I click that button after Submitting, it already says that intProductCounter is zero, so I don’t think it’s an issue with Undo. The only time I set intProductCounter to be zero is at the beginning of Submit.

I have stepped through all my code twice, and done searches on any instances of the variable name. The only places I see it are when I declare it publically at the beginning of the form, in each iteration loop of colors and envelope sizes, at the beginning of Submit when I set it to zero, and at the end of Submit when I create the confirmation MsgBox. And in “Undo”, of course.

I’d love some help on this. The entire form is over 2,000 lines of code, so I can’t post everything. Here are some snippets:

 Public intProductCounter As Integer

…
Private Sub cmdOK_Click()
Dim intProductCounter As Integer
    intProductCounter = 0
…
(inside loop through colors)
ActiveCell.Offset(0, 4).Value = cboMillName & " " & txtBrand & " - " & strProductType
intProductCounter = intProductCounter + 1
ActiveCell.Offset(1, 0).Select
…
(end of OK subroutine)
If intProductCounter = 1 Then
        msgBox "1 Product Added"
    Else
        msgBox intProductCounter & " Products Added"
    End If
	…

Private Sub cmdUndo_Click()

	Dim msgboxResult As Integer
    msgboxResult = msgBox("Are you sure you want to undo your last submission of " & intProductCounter & " products?", vbYesNo, "Confirm Undo")
    If msgboxResult = 7 Then
            Exit Sub
    End If
…
msgBox "You have deleted the last " & intProductCounter & " products from the spreadsheet"

Please let me know if more/different code snippets would help you. Thanks!

 

[PHV]

Some discrepancy here:
Public intProductCounter As Integer
Private Sub cmdOK_Click()
Dim intProductCounter As Integer

you declare a local variable in cmdOK_Click with the same name as the global and thus you've a variable scope issue ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[rabley]

Okay, now that's embarrassing. I had it that way originally, but for some reason, I thought the Debugger required me to declare it again after all. I am clearly showing my newbie-ness to VBA. Thanks for the help!