Need help with some VB code for a word document.

[88keys]

There was a time that I was half decent at this and I'm in the process of re-learning because of some projects my boss has assigned me to....

Anyway, this is basically a form in which you enter your information and the program will automatically format and place the text to be printed on a label sheet. This is done to keep all of the labels uniform and easy to read because some people like to get carried away with artsy fartsy fonts and styles.


Thinking this through as best I could, I wanted something that would be easy for anyone (there are a few fifty-somethings in the dept) to use and worked reliably, so here is the program that I came up with:

• Each cell contains grayed italicized text which indicates the data that belongs in each box.
• If data is entered in the box; the font switches to a black non-italicized Arial font.
• If the data is left blank, the grayed text returns. If the textbox in the lower right contains information entered by the user, a slash will automatically be added before it via a hidden textbox. I tried doing this another way which wasn't working (I'll get to that later) so I had to do it by adding hidden textboxes which are named "TextBox1, TextBox2, etc"

Here is the code for the first 4 textboxes.

Private Sub txtA1_01_AfterUpdate()
    If txtA1_01 = "" Then
        txtA1_01.Font.Italic = True
        txtA1_01 = "Item ID"
        txtA1_01.ForeColor = &H80000011
    End If
End Sub
Private Sub txtA1_01_Enter()
    If txtA1_01 = "Item ID" Then
        txtA1_01 = ""
        txtA1_01.Font.Italic = False
        txtA1_01.ForeColor = &H80000007
    End If
End Sub
Private Sub txtA2_01_AfterUpdate()
    If txtA2_01 = "" Then
        txtA2_01 = "Customer"
        txtA2_01.Font.Italic = True
        txtA2_01.ForeColor = &H80000011
    End If
End Sub
Private Sub txtA2_01_Enter()
    If txtA2_01 = "Customer" Then
        txtA2_01 = ""
        txtA2_01.Font.Italic = False
        txtA2_01.ForeColor = &H80000007
    End If
End Sub
Private Sub txtA3_01_AfterUpdate()
    If txtA3_01 = "" Then
        txtA3_01.Font.Italic = True
        txtA3_01 = "Customer Part Number"
        txtA3_01.ForeColor = &H80000011
    End If
End Sub
Private Sub txtA3_01_Enter()
    If txtA3_01 = "Customer Part Number" Then
        txtA3_01 = ""
        txtA3_01.Font.Italic = False
        txtA3_01.ForeColor = &H80000007
    End If
End Sub
Private Sub txtA4_01_AfterUpdate()
    If txtA4_01 = "" Then
        txtA4_01.Font.Italic = True
        txtA4_01 = "Description"
        txtA4_01.ForeColor = &H80000011
    End If
End Sub
Private Sub txtA4_01_Enter()
    If txtA4_01 = "Description" Then
        txtA4_01 = ""
        txtA4_01.Font.Italic = False
        txtA4_01.ForeColor = &H80000007

And then the slash via hidden textbox which comes later in the scrpit.

If txtA4_01.ForeColor = &H80000007 Then
    TextBox1.Value = " / "
    End If
End Sub

The command button in the middle of the form transfers the data to the respective textboxes. The Textboxes that still contain their default values will not transfer.

Here is the code for the command button.

The code that deletes the cells that still contain their default values so they don't transfer.

Sub cmd01_Click()

    If txtA1_01.ForeColor = &H80000011 Then
    txtA1_01.Value = ""
    End If
    If txtA2_01.ForeColor = &H80000011 Then
    txtA2_01.Value = ""
    End If
    If txtA3_01.ForeColor = &H80000011 Then
    txtA3_01.Value = ""
    End If
    If txtA4_01.ForeColor = &H80000011 Then
    txtA4_01.Value = ""
    End If
    If txtA1_02.ForeColor = &H80000011 Then
    txtA1_02.Value = ""
    End If
    If txtA2_02.ForeColor = &H80000011 Then
    txtA2_02.Value = ""
    End If
    If txtA3_02.ForeColor = &H80000011 Then
    txtA3_02.Value = ""
    End If
    If txtA4_02.ForeColor = &H80000011 Then
    txtA4_02.Value = ""
    End If

And here is the code that transfers the data.

   Application.ScreenUpdating = False
   With ActiveDocument
      .Bookmarks("txt01a").Range.Text = txtA1_01.Value
      .Bookmarks("txt02a").Range.Text = txtA2_01.Value
      .Bookmarks("txt03a").Range.Text = txtA3_01.Value & " " & TextBox1.Value & txtA4_01.Value
      .Bookmarks("txt01a2").Range.Text = txtA1_02.Value
      .Bookmarks("txt02a2").Range.Text = txtA2_02.Value
      .Bookmarks("txt03a2").Range.Text = txtA3_02.Value & " " & TextBox2.Value & txtA4_02.Value
      
    
   End With


   Unload Me
   
   End Sub

Now even though it is technically working, I think it is somewhat bloated and I know that the more code you write, the more likely you are to have problems in the future. This combined with that fact that I was muddling through gives me low confidence with the integrity of my script.
That being said, here are my concerns:

• The way I'm adding the slash (based on the lower right textbox) is via a hidden textbox. I feel like there is a better way to do this but adding the hidden textbox was the only way I could make it work.
• Also my method of data transfer from the textboxes over to the document. I think it would be better to transfer the data when it contains info entered by the use using If/When based on what's in the cell because this would eliminate the need for deletion of untouched textboxes when hitting the Command Button because it would mean less code.
• Then finally there is one minor flaw that I would like to have fixed if possible. Whenever a space is entered in the textboxes, the defualt values won't return. There literally needs to be nothing in the textbox. This in turn will cause the "/" to transfer to the document if a space has been entered in the lower right textbox.

Now I understand that this is a pretty big form to manage (100 textboxes in all) so I'm doing my best to keep things understandable for you guys.

• Here is an example of the form as it appears in full view along with an explanation of naming syntax for the non-hidden textboxes (link)
• Here is an example of how the form transfers data based on the conditions that I described earlier (link)
• Because there is alot of code in this file, I have attached a smaller version of the form only containing only the top 16 textboxes and the 4 hidden textboxes relating to it which should make it easier to review (hopefully)
• FWIW, I also posted the full version of the script.
• There are two zip files. Each one respectively containing the code,, the .frm file, and their respective documents. (LabelTemplate_Full.zip) (LabelTemplate_Small.zip)
• I know I need to fix the tab order with the textboxes, I'm working on that.
• I also know that my bookmark naming syntax isn't 100% consistent. This is because I was having problems with them disappearing for no apparent reason and not working unless I gave renamed them. Next time, I'll make sure to establish bookmarks at final step.

Thanks in advance

 

[bzb_Elder]

I realize you have many questions/issues, but regarding the "space" being entered, you should be able to use trim() to eliminate this one.

Private Sub txtA4_01_AfterUpdate()
If trim(txtA4_01) = "" Then
txtA4_01.Font.Italic = True
txtA4_01 = "Description"
txtA4_01.ForeColor = &H80000011
End If
End Sub

HTH.

 

[Tweak155]

You're much better managing this with arrays and looping through them since the operations are all the same.

This is made even easier with the use of Class Modules which will trap the events you're updating.

Both of these approaches are much more advanced, but simple once you grasp the concept. I think if you google the keyword "WithEvents VBA", you should find info you're looking for. Something like this is simple for me to put together now, but it's long and hard to explain. The "simplest" I can explain is:

1 - You set up a Class Module that basically holds a text box. You declare the TextBox with Public WithEvents tBox as MSForms.TextBox

2 - In the class module, define the events and the same action that you want to happen for every text box

3 - In a loop (likely in the Form Initialize), define a temp variable with the class definition from steps 1 & 2, assign the current text box to the class, then store the class to an array or a collection

This will keep the trapped events alive in memory and it will kick off the code for all the text boxes, regardless of which one the user is working with. This allows you to add as many or remove any text boxes you want, and even add them programatically.

If you want only certain text boxes trapped, then use a naming convention to limit it to those boxes.