[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Word VBA, make variable value persist for each macro instance

Posted on 2006-05-25
3
Medium Priority
?
845 Views
Last Modified: 2008-01-09
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
 
 
0
Comment
Question by:rossh1977
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 16764000
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
 

Author Comment

by:rossh1977
ID: 16769114
Hi Graham hiding the form worked perfectly

Ross
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 16769739
Hi Ross,
Thanks for the feedback and the quick acceptance.
Cheers, Graham
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is written from a 'VBA for MS Word' perspective, but I am sure it applies to most other MS Office components where VBA is used.  One thing that really bugs me is slow code, ESPECIALLY when it's mine!  In programming there are so many ways to…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Suggested Courses
Course of the Month18 days, 22 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question