+ Reply to Thread
Results 1 to 2 of 2

Accessing public variable from form

Hybrid View

  1. #1
    Registered User
    Join Date
    11-29-2012
    Location
    Madison, WI
    MS-Off Ver
    MS Office 2010 (Excel, Word, Access)
    Posts
    72

    Accessing public variable from form

    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?

  2. #2
    Registered User
    Join Date
    11-29-2012
    Location
    Madison, WI
    MS-Off Ver
    MS Office 2010 (Excel, Word, Access)
    Posts
    72

    Re: Accessing public variable from form

    Never mind. I was right, it was dirt simple. I'd delete the thread if I knew how.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Public Variable value
    By nfpaccounting in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-11-2011, 02:34 PM
  2. Public Variable
    By Digitborn.com in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-09-2007, 03:54 PM
  3. [SOLVED] Public variable
    By Jack in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 03-18-2006, 05:40 PM
  4. [SOLVED] declaring public variable value
    By Damon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-24-2005, 10:05 AM
  5. [SOLVED] Problem accessing Oracle Public Synonyms from Microsoft Excel 2002
    By Sandeep in forum Excel General
    Replies: 1
    Last Post: 01-05-2005, 06:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1