VBA: Variable not retaining Value

  • Am trying to run a print procedure from a user form that copies the data in a cell to a variable, then deletes the data from the celll, prints the selected data and then is supposed to restore the data previously deleted. However, the variable data is getting lost after the CAll procedure. See code below[vba]Sub CommandButton2_Click()
    If Check_print_Change Then

    Call Print_Exp_Change_By_month
    MsgBox Temp_variable
    If Print_chk Then
    End If

    If View_chk Then
    Unload print_form
    End If

    Application.ScreenUpdating = True
    'restores the text removed in the called sub routine Print_Exp_Change_by_month
    Range("A10").Value = "Temp_variable"
    Unload print_form

    End If
    End Sub

    Sub Print_Exp_Change_By_month()

    Static Temp_variable As String
    'removes the text from cell A10
    Temp_variable = Range("A10").Value
    Range("A10").Value = "'2003/2004"

    'Selects data for printing and viewing
    With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = "$A:$A"
    End With

    ActiveSheet.PageSetup.PrintArea = "$AH$9:$AK$36"

    With ActiveSheet.PageSetup
    .Orientation = xlPortrait
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    End With
    End Sub[/vba]
    thanks for any help

  • Hi pt,

    If you want to use the same variable in more than 1 sub, you have to declare it Public in the General section of the Module code (the very top before your first sub)

    Public Temp_variable As String

    Then don't Dim the variable in either Sub code.

    Hope this helps


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!