PDA

View Full Version : Save and Open button


m_court15
06-22-2011, 06:18 AM
I am trying to create a userform with a Save As button and Open button. I want the Save As button to save the data to some type of sheet that can be opened at a later time and repopulate the userform. With that said, I want to have an open button in my userform that will open one of these files and repopulate the correct boxes. I need to create a "database" that can be accessed at a later date.
I am kind of at a stand still right now. Any help would be greatly appreciated.

Option Explicit
Private oVars As Variables
Private Sub cmdBack_Click()
'GOES BACK TO MAIN MENU
Unload Me
MainMenu.Show
End Sub
Private Sub cmdAddSave_click()
'POPULATES DOCUMENT WITH VARIABLES
Set oVars = ActiveDocument.Variables
oVars("DieType").Value = "Pultrusion HV Die Inspection form"
oVars("DieNo").Value = Me.cboDieNo
oVars("RunDate").Value = Me.txtRunDate
oVars("DieInternalSuraface").Value = Me.txtDieInternalSuraface
oVars("InternalDamage").Value = Me.txtInternalDamage
oVars("InternalChrome").Value = Me.txtInternalChrome
oVars("InternalSurface").Value = Me.txtInternalSurface
oVars("AFace").Value = Me.txtAFace
oVars("ADamage").Value = Me.txtADamage
oVars("AChrome").Value = Me.txtAChrome
oVars("ASurface").Value = Me.txtASurface
oVars("BFace").Value = Me.txtBFace
oVars("BDamage").Value = Me.txtBDamage
oVars("BChrome").Value = Me.txtBChrome
oVars("BSurface").Value = Me.txtBSurface
oVars("SF4").Value = Me.txtSF4
oVars("SF5").Value = Me.txtSF5
oVars("SF6").Value = Me.txtSF6
oVars("CT4").Value = Me.txtCT4
oVars("CT5").Value = Me.txtCT5
oVars("CT6").Value = Me.txtCT6
oVars("AP1").Value = Me.txtAP1
oVars("AP2").Value = Me.txtAP2
oVars("AP3").Value = Me.txtAP3
oVars("AP4").Value = Me.txtAP4
oVars("AP5").Value = Me.txtAP5
oVars("AP6").Value = Me.txtAp6
oVars("AP7").Value = Me.txtAP7
oVars("AP8").Value = Me.txtAP8
oVars("AP9").Value = Me.txtAP9
oVars("AP10").Value = Me.txtAP10
oVars("AP11").Value = Me.txtAP11
oVars("AP12").Value = Me.txtAP12
oVars("AP13").Value = Me.txtAP13
oVars("AP14").Value = Me.txtAP14
oVars("AP15").Value = Me.txtAP15
oVars("DieCondition").Value = Me.cboDieCondition
oVars("Description").Value = Me.txtDescription
oVars("Size").Value = Me.txtSize

'LEAVES FIELD BLANK IF NOTHING IS ENTERED
If Me.txtAP15 = "" Then
oVars("AP15").Value = " "
Else
oVars("AP15").Value = Me.txtAP15
End If
If Me.txtAP14 = "" Then
oVars("AP14").Value = " "
Else
oVars("AP14").Value = Me.txtAP14
End If
If Me.txtAP13 = "" Then
oVars("AP13").Value = " "
Else
oVars("AP13").Value = Me.txtAP13
End If
If Me.txtAP12 = "" Then
oVars("AP12").Value = " "
Else
oVars("AP12").Value = Me.txtAP12
End If
If Me.txtAP11 = "" Then
oVars("AP11").Value = " "
Else
oVars("AP11").Value = Me.txtAP11
End If
If Me.txtAP10 = "" Then
oVars("AP10").Value = " "
Else
oVars("AP10").Value = Me.txtAP10
End If
If Me.txtAP9 = "" Then
oVars("AP9").Value = " "
Else
oVars("AP9").Value = Me.txtAP9
End If
If Me.txtAP8 = "" Then
oVars("AP8").Value = " "
Else
oVars("AP8").Value = Me.txtAP8
End If
If Me.txtAP7 = "" Then
oVars("AP7").Value = " "
Else
oVars("AP7").Value = Me.txtAP7
End If
If Me.txtAp6 = "" Then
oVars("AP6").Value = " "
Else
oVars("AP6").Value = Me.txtAp6
End If
If Me.txtAP5 = "" Then
oVars("AP5").Value = " "
Else
oVars("AP5").Value = Me.txtAP5
End If
If Me.txtAP4 = "" Then
oVars("AP4").Value = " "
Else
oVars("AP4").Value = Me.txtAP4
End If
If Me.txtAP3 = "" Then
oVars("AP3").Value = " "
Else
oVars("AP3").Value = Me.txtAP3
End If
If Me.txtAP2 = "" Then
oVars("AP2").Value = " "
Else
oVars("AP2").Value = Me.txtAP2
End If
If Me.txtAP1 = "" Then
oVars("AP1").Value = " "
Else
oVars("AP1").Value = Me.txtAP1
End If
If Me.cboDieNo = "" Then
oVars("DieNo").Value = " "
Else
oVars("Dieno").Value = Me.cboDieNo
End If
If Me.txtRunDate = "" Then
oVars("RunDate").Value = " "
Else
oVars("RunDate").Value = Me.txtRunDate
End If
If Me.txtDieInternalSuraface = "" Then
oVars("DieInternalSuraface").Value = " "
Else
oVars("DieInternalSuraface").Value = Me.txtDieInternalSuraface
End If
If Me.txtInternalDamage = "" Then
oVars("InternalDamage").Value = " "
Else
oVars("InternalDamage").Value = Me.txtInternalDamage
End If
If Me.txtInternalChrome = "" Then
oVars("InternalChrome").Value = " "
Else
oVars("InternalChrome").Value = Me.txtInternalChrome
End If
If Me.txtInternalSurface = "" Then
oVars("InternalSurface").Value = " "
Else
oVars("InternalSurface").Value = Me.txtInternalSurface
End If
If Me.txtAFace = "" Then
oVars("AFace").Value = " "
Else
oVars("AFace").Value = Me.txtAFace
End If
If Me.txtADamage = "" Then
oVars("ADamage").Value = " "
Else
oVars("ADamage").Value = Me.txtADamage
End If
If Me.txtAChrome = "" Then
oVars("AChrome").Value = " "
Else
oVars("AChrome").Value = Me.txtAChrome
End If
If Me.txtASurface = "" Then
oVars("ASurface").Value = " "
Else
oVars("ASurface").Value = Me.txtASurface
End If
If Me.txtBFace = "" Then
oVars("BFace").Value = " "
Else
oVars("BFace").Value = Me.txtBFace
End If
If Me.txtBDamage = "" Then
oVars("BDamage").Value = " "
Else
oVars("BDamage").Value = Me.txtBDamage
End If
If Me.txtBChrome = "" Then
oVars("BChrome").Value = " "
Else
oVars("BChrome").Value = Me.txtBChrome
End If
If Me.txtBSurface = "" Then
oVars("BSurface").Value = " "
Else
oVars("BSurface").Value = Me.txtBSurface
End If
If Me.txtCT6 = "" Then
oVars("CT6").Value = " "
Else
oVars("CT6").Value = Me.txtCT6
End If
If Me.txtCT5 = "" Then
oVars("CT5").Value = " "
Else
oVars("CT5").Value = Me.txtCT5
End If
If Me.txtCT4 = "" Then
oVars("CT4").Value = " "
Else
oVars("CT4").Value = Me.txtCT4
End If
If Me.txtSF6 = "" Then
oVars("SF6").Value = " "
Else
oVars("SF6").Value = Me.txtSF6
End If
If Me.txtSF5 = "" Then
oVars("SF5").Value = " "
Else
oVars("SF5").Value = Me.txtSF5
End If
If Me.txtSF4 = "" Then
oVars("SF4").Value = " "
Else
oVars("SF4").Value = Me.txtSF4
End If
If Me.cboDieCondition = "" Then
oVars("DieCondition").Value = " "
Else
oVars("DieCondition").Value = Me.cboDieCondition
End If
If Me.txtDescription = "" Then
oVars("description").Value = " "
Else
oVars("Description").Value = Me.txtDescription
End If
If Me.txtSize = "" Then
oVars("Size").Value = " "
Else
oVars("Size").Value = Me.txtSize
End If
If labelHVDie = "Pultrusion HV Die Inspection Form" Then
ActiveDocument.Bookmarks("sig").Range.InlineShapes.AddPicture "C:\Users\Matt Courtright\Documents\pultrusion form\HV Die.jpg" '.linktofile:=False,savewithdocument:=True
End If
ActiveDocument.Fields.Update
Set oVars = Nothing
Unload Me
'OPENS SAVE AS DIALOG BOX
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogSaveAs)
'SAVE AS FILE NAME AND TYPE
With fd
If cboDieCondition = "Good Two Ends" Then
.InitialFileName = Me.cboDieNo & "_GoodTwoEnds_" & Format(Date, "MMMddYYYY")
End If
If cboDieCondition = "Good One End" Then
.InitialFileName = Me.cboDieNo & "_GoodOneEnd_" & Format(Date, "MMMddYYYY")
End If
If cboDieCondition = "Backup Only" Then
.InitialFileName = Me.cboDieNo & "_BackupOnly_" & Format(Date, "MMMddYYYY")
End If
If cboDieCondition = "Bad" Then
.InitialFileName = Me.cboDieNo & "_Bad_" & Format(Date, "MMMddYYYY")
End If
.InitialFileName = Me.cboDieNo & "_Round_" & Format(Date, "MMMddYYYY")
If .Show = 0 Then 'user pressed the cancel button
Else
.Execute
End If
End With
Set fd = Nothing
End Sub
Private Sub cmdClear_Click()
'CLEARS VALUES
cboDieNo.Value = Null
cboDieCondition.Value = Null
txtSize.Value = Null
txtDescription.Value = Null
txtRunDate.Value = Null
txtDieInternalSuraface.Value = Null
txtInternalDamage.Value = Null
txtInternalChrome.Value = Null
txtInternalSurface.Value = Null
txtAFace.Value = Null
txtADamage.Value = Null
txtAChrome.Value = Null
txtASurface.Value = Null
txtBFace.Value = Null
txtBDamage.Value = Null
txtBChrome.Value = Null
txtBSurface.Value = Null
txtSF4.Value = Null
txtSF5.Value = Null
txtSF6.Value = Null
txtCT4.Value = Null
txtCT5.Value = Null
txtCT6.Value = Null
txtAP1.Value = Null
txtAP2.Value = Null
txtAP3.Value = Null
txtAP4.Value = Null
txtAP5.Value = Null
txtAp6.Value = Null
txtAP7.Value = Null
txtAP8.Value = Null
txtAP9.Value = Null
txtAP10.Value = Null
txtAP11.Value = Null
txtAP12.Value = Null
txtAP13.Value = Null
txtAP14.Value = Null
txtAP15.Value = Null
End Sub
Private Sub cmdCancel_Click()
'CLOSES FORM WITH OUT SAVING
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub
Private Sub UserForm_Initialize()
'ADDS ITEMS TO DROP DOWN BOXES
With cboDieNo
.AddItem "101"
.AddItem "102"
.AddItem "103"
.AddItem "104"
.AddItem "201"
.AddItem "202"
.AddItem "203"
.AddItem "301"
.AddItem "302"
.AddItem "303"
.AddItem "304"
.AddItem "401"
.AddItem "402"
.AddItem "403XXXX"
.AddItem "501"
.AddItem "502"
.AddItem "503"
.AddItem "601"
.AddItem "602"
.AddItem "603"
.AddItem "604"
.AddItem "701"
.AddItem "702"
.AddItem "703"
.AddItem "704"
.AddItem "801"
.AddItem "802"
.AddItem "803"
.AddItem "901"
.AddItem "902"
End With
With cboDieCondition
.AddItem "Good One End"
.AddItem "good Two Ends"
.AddItem "Backup Only"
.AddItem "Bad"
End With
End Sub
Private Sub cboDieNo_Change()
'CHANGES DESCRIPTION DEPENDING ON DIE NO. SELECTION
If cboDieNo.Value = "101" Then
txtDescription.Value = "HV-TP, 1.145 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "102" Then
txtDescription.Value = "HV-TP, 1.145 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "103" Then
txtDescription.Value = "HV-TP, 1.145 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "104" Then
txtDescription.Value = "HV-TP, 1.145 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "201" Then
txtDescription.Value = "HV-TP, 1.30 ID"
txtSize.Value = "5 x 5 x 48"
End If
If cboDieNo.Value = "202" Then
txtDescription.Value = "HV-TP, 1.30 ID"
txtSize.Value = "5 x 5 x 48"
End If
If cboDieNo.Value = "203" Then
txtDescription.Value = "HV-TP, 1.30 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "301" Then
txtDescription.Value = "HV-TP, 1.455 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "302" Then
txtDescription.Value = "HV-TP, 1.455 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "303" Then
txtDescription.Value = "HV-TP, 1.455 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "304" Then
txtDescription.Value = "HV-TP, 1.455 ID"
txtSize.Value = "5 x 5 x 48"
End If
If cboDieNo.Value = "401" Then
txtDescription.Value = "HV-TP, 1.61 ID"
txtSize.Value = "5 x 5 x 48"
End If
If cboDieNo.Value = "402" Then
txtDescription.Value = "HV-TP, 1.61 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "403XXXX" Then
txtDescription.Value = "HV-TP, 1.61 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "501" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "502" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "503" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "601" Then
txtDescription.Value = "HV-TP, 1.92 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "602" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "603" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "604" Then
txtDescription.Value = "HV-TP, 1.765 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "701" Then
txtDescription.Value = "HV-TP, 2.075 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "702" Then
txtDescription.Value = "HV-TP, 2.075 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "703" Then
txtDescription.Value = "HV-TP, 2.075 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "704" Then
txtDescription.Value = "HV-TP, 2.075 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "801" Then
txtDescription.Value = "HV-TP, 2.23 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "802" Then
txtDescription.Value = "HV-TP, 2.23 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "803" Then
txtDescription.Value = "HV-TP, 2.23 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "901" Then
txtDescription.Value = "HV-TP, 2.385 ID"
txtSize.Value = "5 x 5 x 36"
End If
If cboDieNo.Value = "902" Then
txtDescription.Value = "HV-TP, 2.385 ID"
txtSize.Value = "5 x 5 x 36"
End If
End Sub

thanks for your help,
-Matt
Frosty
06-22-2011, 08:13 AM
I'm not at a machine with 2010 right now, but I can look at your code and point out a couple of conceptual points just to make things a bit easier.

You will very much benefit from the use of some functions.

You're manually converting the name of a control to a document variable by removing the three letter identifying prefix... why not write that into a function call? Something along the lines of the following:

'-----------------------------------------------------------------------------------------------
'pass in a control with a three letter prefix, writes to a doc variable
'the value of the control
'-----------------------------------------------------------------------------------------------
Sub WriteControlValueToDocVariable(oFormCtrl As Object, oDoc As Document)
Dim sVal As String
Dim sVarName As String
On Error GoTo l_err
'if it doesn't have a value, this will cause an error
sVal = oFormCtrl.Value

'if we get here, it has a value property, but it's empty
If sVal = "" Then
sVal = " "
End If
'truncate the form control name by 3 letters to get our doc variable name
sVarName = Right(oFormCtrl.Name, Len(oFormCtrl.Name) - 3)
'and then write it
oDoc.Variables(sVarName).Value = sVal
l_exit:
Exit Sub
l_err:
'don't write to it or anything, just go to our exit
Debug.Print "Here's a control to skip: " & oFormCtrl.Name
Resume l_exit
End Sub
'-----------------------------------------------------------------------------------------------
' Cycle through all of the controls on a form, except for some
'-----------------------------------------------------------------------------------------------
Sub CycleThroughFormControls()
'set the name of your form here
Dim f As oMyForm
Dim oFormCtrl As Object

'set the name here
Set f = New oMyForm

For Each oFormCtrl In f.Controls
Select Case oFormCtrl.Name
Case "txtException"
'don't process it
Case Else
'process it
WriteControlValueToDocVariable oFormCtrl, ActiveDocument
End Select
Next

End Sub


Also, investigate the use of select case (I use it in one of the routines above). That's going to pay you big dividends when you use something like:

Select Case cboDieNo.Value
Case "401"
txtDescription.Value = "HV-TP, 1.61 ID"
txtSize.Value = "5 x 5 x 48"
Case "402"
txtDescription.Value = "HV-TP, 1.61 ID"
txtSize.Value = "5 x 5 x 36"
Case Else
txtDescription.Value = "Unknown!!"
txtSize.Value = "Unknown!!"
End Select


Instead of what you currently have. You can also switch to using an array to keep your data even simpler, but we'll get there.

You've got a big topic here, and it doesn't look (to me) like the current code you have works, but let's tackle the process in small chunks.

First, let's try to simplify what you have... then we'll get to modularizing your code. This is not a particularly simple task to learn the A to Z on.
m_court15
06-22-2011, 10:53 AM
Wow, thanks for the quick reply. I will attempt to translate what you wrote into my userform. I am very new at this so please bare with me.

I will see what I come up with and report back.

Any input is greatly appreaciated.
m_court15
06-23-2011, 07:55 AM
So this:
'-----------------------------------------------------------------------------------------------
'pass in a control with a three letter prefix, writes to a doc variable
'the value of the control
'-----------------------------------------------------------------------------------------------
Sub WriteControlValueToDocVariable(oFormCtrl As Object, oDoc As Document)
Dim sVal As String
Dim sVarName As String
On Error Goto l_err
'if it doesn't have a value, this will cause an error
sVal = oFormCtrl.Value

'if we get here, it has a value property, but it's empty
If sVal = "" Then
sVal = " "
End If
'truncate the form control name by 3 letters to get our doc variable name
sVarName = Right(oFormCtrl.Name, Len(oFormCtrl.Name) - 3)
'and then write it
oDoc.Variables(sVarName).Value = sVal
l_exit:
Exit Sub
l_err:
'don't write to it or anything, just go to our exit
Debug.Print "Here's a control to skip: " & oFormCtrl.Name
Resume l_exit
End Sub
'-----------------------------------------------------------------------------------------------
' Cycle through all of the controls on a form, except for some
'-----------------------------------------------------------------------------------------------
Sub CycleThroughFormControls()
'set the name of your form here
Dim f As oMyForm
Dim oFormCtrl As Object

'set the name here
Set f = New oMyForm

For Each oFormCtrl In f.Controls
Select Case oFormCtrl.Name
Case "txtException"
'don't process it
Case Else
'process it
WriteControlValueToDocVariable oFormCtrl, ActiveDocument
End Select
Next

End Sub

Is Suppose to replace this?Option Explicit
Private oVars As Variables
Private Sub cmdBack_Click()
'GOES BACK TO MAIN MENU
Unload Me
MainMenu.Show
End Sub
Private Sub cmdAddSave_click()
'POPULATES DOCUMENT WITH VARIABLES
Set oVars = ActiveDocument.Variables
oVars("DieType").Value = "Pultrusion HV Die Inspection form"
oVars("DieNo").Value = Me.cboDieNo
oVars("RunDate").Value = Me.txtRunDate
oVars("DieInternalSuraface").Value = Me.txtDieInternalSuraface
oVars("InternalDamage").Value = Me.txtInternalDamage
oVars("InternalChrome").Value = Me.txtInternalChrome
oVars("InternalSurface").Value = Me.txtInternalSurface
oVars("AFace").Value = Me.txtAFace
oVars("ADamage").Value = Me.txtADamage
oVars("AChrome").Value = Me.txtAChrome
oVars("ASurface").Value = Me.txtASurface
oVars("BFace").Value = Me.txtBFace
oVars("BDamage").Value = Me.txtBDamage
oVars("BChrome").Value = Me.txtBChrome
oVars("BSurface").Value = Me.txtBSurface
oVars("SF4").Value = Me.txtSF4
oVars("SF5").Value = Me.txtSF5
oVars("SF6").Value = Me.txtSF6
oVars("CT4").Value = Me.txtCT4
oVars("CT5").Value = Me.txtCT5
oVars("CT6").Value = Me.txtCT6
oVars("AP1").Value = Me.txtAP1
oVars("AP2").Value = Me.txtAP2
oVars("AP3").Value = Me.txtAP3
oVars("AP4").Value = Me.txtAP4
oVars("AP5").Value = Me.txtAP5
oVars("AP6").Value = Me.txtAp6
oVars("AP7").Value = Me.txtAP7
oVars("AP8").Value = Me.txtAP8
oVars("AP9").Value = Me.txtAP9
oVars("AP10").Value = Me.txtAP10
oVars("AP11").Value = Me.txtAP11
oVars("AP12").Value = Me.txtAP12
oVars("AP13").Value = Me.txtAP13
oVars("AP14").Value = Me.txtAP14
oVars("AP15").Value = Me.txtAP15
oVars("DieCondition").Value = Me.cboDieCondition
oVars("Description").Value = Me.txtDescription
oVars("Size").Value = Me.txtSize

'LEAVES FIELD BLANK IF NOTHING IS ENTERED
If Me.txtAP15 = "" Then
oVars("AP15").Value = " "
Else
oVars("AP15").Value = Me.txtAP15
End If
If Me.txtAP14 = "" Then
oVars("AP14").Value = " "
Else
oVars("AP14").Value = Me.txtAP14
End If
If Me.txtAP13 = "" Then
oVars("AP13").Value = " "
Else
oVars("AP13").Value = Me.txtAP13
End If
If Me.txtAP12 = "" Then
oVars("AP12").Value = " "
Else
oVars("AP12").Value = Me.txtAP12
End If
If Me.txtAP11 = "" Then
oVars("AP11").Value = " "
Else
oVars("AP11").Value = Me.txtAP11
End If
If Me.txtAP10 = "" Then
oVars("AP10").Value = " "
Else
oVars("AP10").Value = Me.txtAP10
End If
If Me.txtAP9 = "" Then
oVars("AP9").Value = " "
Else
oVars("AP9").Value = Me.txtAP9
End If
If Me.txtAP8 = "" Then
oVars("AP8").Value = " "
Else
oVars("AP8").Value = Me.txtAP8
End If
If Me.txtAP7 = "" Then
oVars("AP7").Value = " "
Else
oVars("AP7").Value = Me.txtAP7
End If
If Me.txtAp6 = "" Then
oVars("AP6").Value = " "
Else
oVars("AP6").Value = Me.txtAp6
End If
If Me.txtAP5 = "" Then
oVars("AP5").Value = " "
Else
oVars("AP5").Value = Me.txtAP5
End If
If Me.txtAP4 = "" Then
oVars("AP4").Value = " "
Else
oVars("AP4").Value = Me.txtAP4
End If
If Me.txtAP3 = "" Then
oVars("AP3").Value = " "
Else
oVars("AP3").Value = Me.txtAP3
End If
If Me.txtAP2 = "" Then
oVars("AP2").Value = " "
Else
oVars("AP2").Value = Me.txtAP2
End If
If Me.txtAP1 = "" Then
oVars("AP1").Value = " "
Else
oVars("AP1").Value = Me.txtAP1
End If
If Me.cboDieNo = "" Then
oVars("DieNo").Value = " "
Else
oVars("Dieno").Value = Me.cboDieNo
End If
If Me.txtRunDate = "" Then
oVars("RunDate").Value = " "
Else
oVars("RunDate").Value = Me.txtRunDate
End If
If Me.txtDieInternalSuraface = "" Then
oVars("DieInternalSuraface").Value = " "
Else
oVars("DieInternalSuraface").Value = Me.txtDieInternalSuraface
End If
If Me.txtInternalDamage = "" Then
oVars("InternalDamage").Value = " "
Else
oVars("InternalDamage").Value = Me.txtInternalDamage
End If
If Me.txtInternalChrome = "" Then
oVars("InternalChrome").Value = " "
Else
oVars("InternalChrome").Value = Me.txtInternalChrome
End If
If Me.txtInternalSurface = "" Then
oVars("InternalSurface").Value = " "
Else
oVars("InternalSurface").Value = Me.txtInternalSurface
End If
If Me.txtAFace = "" Then
oVars("AFace").Value = " "
Else
oVars("AFace").Value = Me.txtAFace
End If
If Me.txtADamage = "" Then
oVars("ADamage").Value = " "
Else
oVars("ADamage").Value = Me.txtADamage
End If
If Me.txtAChrome = "" Then
oVars("AChrome").Value = " "
Else
oVars("AChrome").Value = Me.txtAChrome
End If
If Me.txtASurface = "" Then
oVars("ASurface").Value = " "
Else
oVars("ASurface").Value = Me.txtASurface
End If
If Me.txtBFace = "" Then
oVars("BFace").Value = " "
Else
oVars("BFace").Value = Me.txtBFace
End If
If Me.txtBDamage = "" Then
oVars("BDamage").Value = " "
Else
oVars("BDamage").Value = Me.txtBDamage
End If
If Me.txtBChrome = "" Then
oVars("BChrome").Value = " "
Else
oVars("BChrome").Value = Me.txtBChrome
End If
If Me.txtBSurface = "" Then
oVars("BSurface").Value = " "
Else
oVars("BSurface").Value = Me.txtBSurface
End If
If Me.txtCT6 = "" Then
oVars("CT6").Value = " "
Else
oVars("CT6").Value = Me.txtCT6
End If
If Me.txtCT5 = "" Then
oVars("CT5").Value = " "
Else
oVars("CT5").Value = Me.txtCT5
End If
If Me.txtCT4 = "" Then
oVars("CT4").Value = " "
Else
oVars("CT4").Value = Me.txtCT4
End If
If Me.txtSF6 = "" Then
oVars("SF6").Value = " "
Else
oVars("SF6").Value = Me.txtSF6
End If
If Me.txtSF5 = "" Then
oVars("SF5").Value = " "
Else
oVars("SF5").Value = Me.txtSF5
End If
If Me.txtSF4 = "" Then
oVars("SF4").Value = " "
Else
oVars("SF4").Value = Me.txtSF4
End If
If Me.cboDieCondition = "" Then
oVars("DieCondition").Value = " "
Else
oVars("DieCondition").Value = Me.cboDieCondition
End If
If Me.txtDescription = "" Then
oVars("description").Value = " "
Else
oVars("Description").Value = Me.txtDescription
End If
If Me.txtSize = "" Then
oVars("Size").Value = " "
Else
oVars("Size").Value = Me.txtSize
End If
If labelHVDie = "Pultrusion HV Die Inspection Form" Then
ActiveDocument.Bookmarks("sig").Range.InlineShapes.AddPicture "C:\Users\Matt Courtright\Documents\pultrusion form\HV Die.jpg" '.linktofile:=False,savewithdocument:=True
End If
ActiveDocument.Fields.Update
Set oVars = Nothing
Unload Me
This means I need to change the prefix on my userform text boxes?
Frosty
06-23-2011, 08:08 AM
No, the CycleThroughFormControls routine is just showing you a proof of concept.

The WriteControlValueToDocVariable will probably have more value, although neither of these routines are meant to do anything but demonstrate functionality.

You do not need to change the prefix on your userform text boxes.

I'm simply pointing out that instead of a long long list prone to typos ("DieInternalSuraface"?), you can, instead, use a loop to go through all of the controls on a passed form object, use the .Name property to figure out which (if any) you want to exclude (since you don't need doc variables for your OK button, as an example), and then use the .Name property (with some string manipulation) to create the exact docvariable you wish to save.

So the the control called txtAP11 is
a) used to determine if you want to "use" this control (by virtue of it's .Name property)
b) transformed into "AP11" for the purposes of your docvariable (string manipulation)
c) checked for the value, and put into a docvariable, inserting a " " instead of a "" if the .Value is an emptry string.

This is a complex bit of code you have all lumped into a subroutine. Breaking your entire process into some smaller chunks is going to help you learn how to do this (as well as troubleshoot).

No whole-sale replacement code really possible... as your code sample is doing too much for me (at least) to feel comfortable giving you a whole-sale replacement (and, also, the time is not incidental).
Frosty
06-23-2011, 08:21 AM
The reason why the For Loop concept is useful here is because, as you add a control (for example) to your userform... as long as you name it properly, that will be the only thing you need to change in your code.

If you add a control the the userform and it needs to be excluded, then you would also need to find that location in the code (and if that happened very often, maybe that wouldn't be the best approach).

There are many many correct ways to structure code. The only incorrect way to structure code is to *not* structure code.

Mostly, the purpose in structuring code is to separate out the "Data" from the "Doings" as clearly as possible. When you post lengthy code like that... it makes our eyes bleed as much as it makes your eyes bleed. Programmers are human beings! Grin.

But you have this long long list of various controls, and you're doing exactly the same thing for each control.

And then at the very very end, you have a little portion of "oh yeah, insert a graphic, update some fields, and unload the form"

There are other conceptual issues to address here, but I thought I'd just start with the first one: the concept of Structured Code.

I'm hoping to help you help yourself... rather than just rewrite your project for you. You've adequately described your concept in your first post... but I think at the end of the day, you're going to want to separate out your functionality like so:

1. Function which takes a control, and saves the control value into a docvariable
2. Function which takes a docvariable, and spits the value into an appropriate control
3. A function which cycles through all of the controls on the form, determining which are "useful"
4. A function which handles the actual document (inserting graphics, updating fields)
5. A function which calls your userform, and populates it from the activedocuemnt.

However, to get there for free, you're going to have to do some of the work :)

Hope this helps!