This is probably dirt simple, but I'm stumped all the same.
I have two forms in the spreadsheet that I'm developing. One is an interface form used on startup so that users only have to look at the data they need. The other is used for data entry to the spreadsheet, with a Multipage that inputs header data from the first tab, and batch data from the second.
I'm trying to set it up so that the page that comes up on the second form changes based on how it's accessed. In order to do that, I've declared public variable intHeadBat in the interface form that is supposed to hold on to the page number it should access in the data entry form, like so.
Interface form:
Public datMyDate As Date
Public intHeadBat As Integer
Public Sub cmdCreBat_Click()
intHeadBat = 1
DepositMaker.Show
End Sub
Public Sub cmdHeader_click()
intHeadBat = 0
DepositMaker.Show
End Sub
Private Sub cmdSetDat_Click()
datMyDate = CDate(InputBox("Input date in MM/DD/YYYY form.", "Custom Date", CStr(Date)))
lblDate.Caption = CStr(datMyDate)
End Sub
Private Sub UserForm_Initialize()
datMyDate = Date
lblDate.Caption = CStr(datMyDate)
End Sub
Private Sub cmdClose_Click()
If MsgBox(Prompt:="Jason, dummy, is this you?", Buttons:=vbYesNo) = vbYes Then
Unload Me
Else
ThisWorkbook.Close SaveChanges:=True
End If
End Sub
Data Entry form:
Dim i As Integer
Private Sub UserForm_Initialize()
SetObj
With cboAcctType
.AddItem "Other"
.AddItem "Common"
.AddItem "Excise"
.AddItem "Fees"
.AddItem "Holding"
.AddItem "Lottery"
.AddItem "Motor Fuel"
.AddItem "Refunds"
.AddItem "SLF"
.AddItem "Unclaimed Property"
End With
MultiPage1.Value = intHeadBat
txtAcctDate.Value = CStr(Date)
txtRecDate.Value = CStr(Date)
End Sub
Private Sub cboAcctType_Change()
cboAcctName.Clear
cboAcctName.Enabled = True
If cboAcctType.Value <> "Other" Then
txtBU.Visible = False
txtAcct.Visible = False
txtProg.Visible = False
txtProj.Visible = False
txtAppr.Visible = False
txtDept.Visible = False
txtFund.Visible = False
lblBUVal.Visible = True
lblAcctVal.Visible = True
lblProgVal.Visible = True
lblApprVal.Visible = True
lblDeptVal.Visible = True
lblFundVal.Visible = True
With cboAcctName
For i = 1 To wsAcctList.Cells(1000000, 14).End(xlUp).Row
If wsAcctList.Cells(i, 14) = cboAcctType.Value Then .AddItem wsAcctList.Cells(i, 2)
Next
End With
Else
txtBU.Visible = True
txtAcct.Visible = True
txtProg.Visible = True
txtProj.Visible = True
txtAppr.Visible = True
txtDept.Visible = True
txtFund.Visible = True
cboAcctName.Enabled = False
lblBUVal.Visible = False
lblAcctVal.Visible = False
lblProgVal.Visible = False
lblApprVal.Visible = False
lblDeptVal.Visible = False
lblFundVal.Visible = False
End If
If cboAcctType.Value = "Lottery" Then txtProj.Visible = True
End Sub
Private Sub cboAcctName_Change()
For i = 1 To wsAcctList.Cells(1000000, 1).End(xlUp).Row
If wsAcctList.Cells(i, 2) = cboAcctName.Value Then Exit For
Next
txtBU = wsAcctList.Cells(i, 4)
txtAcct = wsAcctList.Cells(i, 8)
txtProg = wsAcctList.Cells(i, 9)
txtAppr = wsAcctList.Cells(i, 6)
txtDept = wsAcctList.Cells(i, 7)
txtFund = wsAcctList.Cells(i, 5)
lblBUVal = wsAcctList.Cells(i, 4)
lblAcctVal = wsAcctList.Cells(i, 8)
lblProgVal = wsAcctList.Cells(i, 9)
lblApprVal = wsAcctList.Cells(i, 6)
lblDeptVal = wsAcctList.Cells(i, 7)
lblFundVal = wsAcctList.Cells(i, 5)
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub
Private Sub cmdHeader_click()
wsProcessor.Cells(5, 2) = CDate(txtAcctDate)
wsProcessor.Cells(6, 2) = txtBnkCode
wsProcessor.Cells(7, 2) = txtBnkAcct
wsProcessor.Cells(8, 2) = txtDepType
wsProcessor.Cells(10, 2) = lblGrandTotVal.Caption
wsProcessor.Cells(12, 2) = CDate(txtRecDate)
wsProcessor.Cells(5, 4) = txtCurr
wsProcessor.Cells(10, 4) = txtAcctDate
End Sub
Private Sub cmdSaveDay_Click()
If Dir(PathName:="\\files2\esd_fms\Macro Requests\STAR OneOff\DailyDeposit " & CStr(Date) & ".xlsm") = "" Then
For i = 2 To wsBatches.Cells(1000000, 1).End(xlUp).Row
wsBatches.Cells(i, 5) = "X"
Next
ThisWorkbook.SaveAs Filename:="DailyDeposit " & CStr(Date), FileFormat:=52
Else
LoadBatches
End If
End Sub
For whatever reason, when it opens up the second form to start initializing everything, it doesn't seem to know that intHeadBat has a value in it. What am I missing here? Shouldn't it be accessible as a public variable based on the initial declaration?
Bookmarks