Word VBA, make variable value persist for each macro instance

Hi,

I have a word document which on open displays a userForm asking them to choose 1 of 5 options.  Then user makes their choice and selects ok.  That value is then passed to a function and depending on the value the appropriate colour is assisgned.  This value is then passed to a macro and a table is produced with the cells coloured appropriatley.  My problem is that when the user then needs another table (which they can select from the toolbar) the sub is then looking for the variable value which was passed previously.  How can i make the value persist until the document is closed.  Until now i've been doing this in a document, but i've just tried to save it as a template file, but my userForm on startup has stopped working, any ideas i would be really grateful.

Thanks

Ross

frm_select_document_type
-------------------------------------------------------
Private Sub frm_select_document_type_Initialize()
    opt_evaluation.Value = True
End Sub

Private Sub cmd_cancel_Click()
    Unload Me
    ActiveDocument.Close SaveChanges:=False
End Sub

Private Sub cmd_ok_Click()
    Dim document_type As String

    If opt_evaluation = True Then document_type = "evaluation"
    If opt_assessment = True Then document_type = "assessment"
    If opt_guidance_learning = True Then document_type = "guidance_learning"
    If opt_learning = True Then document_type = "learning"
    If opt_teaching = True Then document_type = "teaching"
       
    Application.ScreenUpdating = False
    'ActiveDocument.Bookmarks("document_type").Range.Text = document_type
    getValue (document_type)
    'create_table (document_type)
    Application.ScreenUpdating = True
    Unload Me
End Sub


module
-------------------------------------------------------
Function getValue(document_type As String)
Dim document_type_colour As Long    
Dim this_doc As String
   
    this_doc = document_type
       
    Select Case this_doc
        Case "evaluation"
            document_type_colour = 8139541
        Case "assessment"
            document_type_colour = 13756773
        Case "guidance_learning"
            document_type_colour = 4122305
        Case "learning"
            document_type_colour = 12559974
        Case "teaching"
            document_type_colour = 8139541
    End Select
   
    Call ActiveDocument.create_table(document_type_colour)
End Function

thisDocument
--------------------------------------------------------------------
Private Sub Document_Open()
   frm_select_document_type.Show
End Sub

Sub create_table(document_type_colour)
    Dim tbl As Word.Table
    Dim numRows As Long
    Dim numCols As Long
   
    Selection.EndKey Unit:=wdStory
   
    numRows = 2
    numCols = 1
    Selection.TypeParagraph
    Set tbl = ActiveDocument.Tables.Add(Selection.Range, numRows, numCols, wdWord8TableBehavior)
   
    With tbl.Rows(1)
        .Shading.BackgroundPatternColor = document_type_colour
        Selection.Font.Name = "Verdana"
        Selection.Font.Size = 10
        Selection.Font.Bold = wdToggle
        Selection.Font.Color = 14809080 'Light yellow
        .Height = InchesToPoints(0.5)
        .Cells.VerticalAlignment = wdCellAlignVerticalCenter
        Selection.MoveDown Unit:=wdLine, Count:=1
    End With
   
    With tbl.Rows(2)
        .Shading.BackgroundPatternColor = 14809080 'Light yellow
        Selection.Font.Name = "Verdana"
        Selection.Font.Size = 10
        .Height = InchesToPoints(0.5)
        .Cells.VerticalAlignment = wdCellAlignVerticalCenter
        Selection.MoveDown Unit:=wdLine, Count:=1
    End With
    Selection.TypeParagraph
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
    "javascript:history.back()", SubAddress:="", ScreenTip:="", TextToDisplay:="Go Back"
    Selection.TypeParagraph
    Selection.MoveUp Unit:=wdLine, Count:=3
    ActiveDocument.Content.Font.Name = "Verdana"
    ActiveDocument.Content.Font.Size = 10

End Sub
 
 Send to a Friend     Printer Friendly  
   
 Comment from rossh1977
Date: 05/25/2006 08:58AM PDT
 Your Comment  


Hi i've been playing around with variables and i've changed things about a bit, i think i'm on the right track but it's not quite working yet!  Would really appreciate someone's help, thanks R

frm_select_document_type
---------------------------------------
Private Sub frm_select_document_type_Initialize()
    opt_evaluation.Value = True
End Sub

Private Sub cmd_cancel_Click()
    Unload Me
    ActiveDocument.Close SaveChanges:=False
End Sub

Private Sub cmd_ok_Click()
    Dim document_type As String

    If opt_evaluation = True Then document_type = "evaluation"
    If opt_assessment = True Then document_type = "assessment"
    If opt_guidance_learning = True Then document_type = "guidance_learning"
    If opt_learning = True Then document_type = "learning"
    If opt_teaching = True Then document_type = "teaching"
       
    Application.ScreenUpdating = False
    If ActiveDocument.Variables.Count > 0 Then
        Call create_table
    Else
        ActiveDocument.Variables.Add "doc_type", document_type '**document type now being set to a variable**
        Call create_table
    End If
    Application.ScreenUpdating = True
    Unload Me
End Sub

thisDocument
-------------------------------------------------
Private Sub Document_Open() 'Document_New()
    frm_select_document_type.Show
End Sub

Private Sub Document_Close()

    'ActiveDocument.Variables("doc_type").Delete
    'ActiveDocument.Variables("doc_type_colour").Delete

End Sub

Sub create_table()
    Dim tbl As Word.Table
    Dim numRows As Long
    Dim numCols As Long
    Dim document_type As String
    Dim document_type_colour As Long
    Dim my_colour As Long
   
   document_type = ActiveDocument.Variables("doc_type").Value
   
   MsgBox "fgfgfg" & document_type
       
    Select Case document_type
        Case "evaluation"
            document_type_colour = 8139541
        Case "assessment"
            document_type_colour = 13756773
        Case "guidance_learning"
            document_type_colour = 4122305
        Case "learning"
            document_type_colour = 12559974
        Case "teaching"
            document_type_colour = 8139541
    End Select
   
    ActiveDocument.Variables.Add "this_colour", document_type_colour
    my_colour = ActiveDocument.Variables("this_colour").Value
   
    Selection.EndKey Unit:=wdStory
   
    numRows = 2
    numCols = 1
    Selection.TypeParagraph
    Set tbl = ActiveDocument.Tables.Add(Selection.Range, numRows, numCols, wdWord8TableBehavior)
   
    With tbl.Rows(1)
        .Shading.BackgroundPatternColor = my_colour
        Selection.Font.Name = "Verdana"
        Selection.Font.Size = 10
        Selection.Font.Bold = wdToggle
        Selection.Font.Color = 14809080 'Light yellow
        .Height = InchesToPoints(0.5)
        .Cells.VerticalAlignment = wdCellAlignVerticalCenter
        Selection.MoveDown Unit:=wdLine, Count:=1
    End With
   
    With tbl.Rows(2)
        .Shading.BackgroundPatternColor = 14809080 'Light yellow
        Selection.Font.Name = "Verdana"
        Selection.Font.Size = 10
        .Height = InchesToPoints(0.5)
        .Cells.VerticalAlignment = wdCellAlignVerticalCenter
        Selection.MoveDown Unit:=wdLine, Count:=1
    End With
    Selection.TypeParagraph
    ActiveDocument.Hyperlinks.Add Anchor:=Selection.Range, Address:= _
    "javascript:history.back()", SubAddress:="", ScreenTip:="", TextToDisplay:="Go Back"
    Selection.TypeParagraph
    Selection.MoveUp Unit:=wdLine, Count:=4
    ActiveDocument.Content.Font.Name = "Verdana"
    ActiveDocument.Content.Font.Size = 10
    'ActiveDocument.Protect
End Sub
 
 
rossh1977Asked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
You could declare the variables as Public in the Declarations section of a module and/or you could Hide the UserForm instead of unloading it.
0
 
rossh1977Author Commented:
Hi Graham hiding the form worked perfectly

Ross
0
 
GrahamSkanRetiredCommented:
Hi Ross,
Thanks for the feedback and the quick acceptance.
Cheers, Graham
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.