PDA

View Full Version : Help with Public Variable Not Retaining Value



CdyMan
10-18-2013, 08:59 AM
I have a userform in which I set a variable that needs to be retained when the form closes, then be available when it re-opens. I've declared it as Public in the form's Initialize sub, but each time the form opens the variable has lost it's value (back to 0). Declaring as static doesn't seem to do the trick either. Can anyone help?? Thanks very much.

Kenneth Hobs
10-18-2013, 09:17 AM
Define it in a Module and not in the Userform which is a private object.

CdyMan
10-18-2013, 09:33 AM
No difference--still getting 0 when macro is re-run.

Kenneth Hobs
10-18-2013, 09:58 AM
You will find that most of my solutions are tested. Post your workbook or a simplified workbook to show your problem. When troubleshooting a problem, try to start simple.

CdyMan
10-18-2013, 10:25 AM
What I need to do is enter a line in the check register when the form is first opened on the 1st of the month, set a flag (day1) that says it's done, so that if the form is then closed and later re-opened (on the 1st), I don't get a repeat entry. The 18th (Today) is being used to test--normally it would be 1.

Module2:


Option Explicit
Public day1 As Integer

Sub Chking()
ufmChecking.Show
End Sub

Code in ufmChecking (Initialize):


If Day(Now) > 18 Then day1 = 0
If Day(Now) = 18 And day1= 0 Then
idxr = wks.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0).row
Set rng = wks.Range("A" & idxr, "J" & idxr)
wks.Range("A" & idxr) = Date
wks.Range("E" & idxr) = "Balance Forward"
wks.Range("J" & idxr) = wks.Cells(Rows.Count, 10).End(xlUp).Offset(0, 0).Value
For Each cell In rng
cell.Interior.ColorIndex = 8
Next
day1 = 1
End If

Kenneth Hobs
10-18-2013, 12:30 PM
Please use code tags when posting code.

Now that you have properly defined what you need rather than telling us how to solve the problem, I know what you need. What you need is an external source to store values. Offhand, I can think of 5 ways.
1. Name
2. Range
3. Document Property
4. Registry
5. External Files

I did not need to Dim n outside of the procedure. I just did that to make it easy for you to reuse it if needed elsewhere. Here is method 1. Obviously, you need a textbox and a commandbutton in the userform to test this.

Dim n As Name

Private Sub UserForm_Initialize()
On Error Resume Next
Set n = ThisWorkbook.Names("ken")
If n Is Nothing Then ThisWorkbook.Names.Add "ken", "=0", False
Set n = ThisWorkbook.Names("ken")
End Sub

Private Sub CommandButton1_Click()
If TextBox1.Value = Empty Then GoTo EndNow
n.Value = "=" & (CDbl(Replace(n.RefersTo, "=", Empty)) + CDbl(TextBox1.Value))
MsgBox n.Value

EndNow:
Unload Me
End Sub

CdyMan
10-18-2013, 12:53 PM
Thanks for the help Ken.