Word 2003 doc/template to form VB(A?) 6.5

Question

dc_fdl 0 Newbie Poster

13 Years Ago

I was tasked with taking our current mail out enrollment form and converting it to a template that will be downloadable from our website. The customer will be able to fill out the form via 23 textboxes, 4 option buttons, 3 comboboxes as dropdowns and clicking one button that attaches the completed form to an OL email and sending to us. It is simple and only one page in length.

I have a lot if not most of this done but have a problem or two (or more that I don't know of).

The form is working to some extent but when the email button is clicked that part of the program saves the complete form (which is ok) but then the next time the form is opened, all of the info from the previous use is still there; I need it to be a clean slate - reset -cleared etc.

I am NOT a programmer and have learned what I know to this point on the fly.

If there is someone willing to help I am pasting my exact code as it currently exists and maybe you can tell me what I need to complete this task. I want to believe that it is something simple that I am missing but have no clue since I don't know VB.

CODE:

Private Sub EnrollmentForm_Activate()

ComboBox1 = "PROGRAMMING_LIST"
ComboBox2 = "MECHANICAL_LIST"
ComboBox3 = "ELECTRICAL_LIST"
CreditCard1 = False
CreditCard2 = False
CreditCard3 = False
CreditCard4 = False
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
TextBox6 = ""
TextBox7 = ""
TextBox8 = ""
TextBox9 = ""
TextBox10 = ""
TextBox11 = ""
TextBox12 = ""
TextBox13 = ""
TextBox14 = ""
TextBox15 = ""
TextBox16 = ""
TextBox17 = ""
TextBox18 = ""
TextBox19 = ""
TextBox20 = ""
TextBox21 = ""
TextBox22 = ""
TextBox23 = ""

End Sub
Private Sub ComboBox1_DropButtonClick()

Me.ComboBox1.List = Split("PROGRAMMING_LIST Fanuc_Horizontal_Milling_March14-18 Fanuc_Horizontal_Milling_June20-24 Fanuc_Horizontal_Milling_September12-16 Fanuc_Horizontal_Milling_December5-9 Fanuc_Vertical_Turning_January_31-February_4 Fanuc_Vertical_Turning_June_20-24 Fanuc_Vertical_Turning_September_12-16 Fanuc_Vertical_Turning_December_5-9 Siemens_840D_Standard_Programming_Horizontal_Milling_March_21-25 Siemens_840D_Standard_Programming_Horizontal_Milling_June_27-July_1 Siemens_840D_Standard_Programming_Horizontal_Milling_September_26-30 Siemens_840D_Standard_Programming_Horizontal_Milling_December_12-16 Siemens_840D_Standard_Programming_Vertical_Turning_February_21-25 Siemens_840D_Standard_Programming_Vertical_Turning_May_23-27 Siemens_840D_Standard_Programming_Vertical_Turning_August_15-19 Siemens_840D_Standard_Programming_Vertical_Turning_November_28-December_2 Siemens_840D_Advanced_Programming_All_Machines_March_1-4 Siemens_840D_Advanced_Programming_All_Machines_September_20-23 SPECIAL_REQUEST")

End Sub
Private Sub ComboBox2_DropButtonClick()

Me.ComboBox2.List = Split("MECHANICAL_LIST Mechanical_HBM_480/485_January_24-28 Mechanical_HBM_480/485_April_25-29 Mechanical_HBM_480/485_July_18-22 Mechanical_HBM_480/485_October_31-November_4 Mechanical_HBM_486_Consult_Factory Mechanical_HMC_568_February_14-18 Mechanical_VTC_524/525/526_March_7-11 Mechanical_VTC_524/525/526_June_6-10 Mechanical_VTC_524/525/526_September_12-16 Mechanical_VTC_524/525/526_December_5-9 Mechanical_VTC_523_Consult_Factory SPECIAL_REQUEST")

End Sub
Private Sub ComboBox3_DropButtonClick()

Me.ComboBox3.List = Split("ELECTRICAL_LIST Electrical_Fanuc_310i_January_17-21 Electrical_Fanuc_310i_April_4-8 Electrical_Fanuc_310i_July_11-15 Electrical_Fanuc_310i_October_3-7 Electrical_Siemens_840D_February_7-11 Electrical_Siemens_840D_May_2-6 Electrical_Siemens_840D_August_1-5 Electrical_Siemens_840D_November_7-11 SPECIAL_REQUEST")

End Sub
Private Sub CommandButton1_Click()
Dim OL As Object
Dim EmailItem As Object
Dim Doc As Document
Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Doc = ActiveDocument
Doc.Save
With EmailItem
.Subject = "MAG Fond du Lac Training Enrollment"
.Body = "Training Administrator," & vbCrLf & vbCrLf & _
"Please enroll our employee in your customer training class per the attached form" & vbCrLf & vbCrLf & _
"Thank You"
.To = "engineeringtrain_do@mag-ias.com"
.Importance = olImportanceNormal
.Attachments.Add Doc.FullName
.Display
' .Send
End With

Application.ScreenUpdating = True
Set Doc = Nothing
Set OL = Nothing
Set EmailItem = Nothing
End Sub
Private Sub CreditCard1_Click()

End Sub
Private Sub CreditCard2_Click()

End Sub
Private Sub CreditCard3_Click()

End Sub
Private Sub CreditCard4_Click()

End Sub
Private Sub TextBox1_Change()

End Sub
Private Sub TextBox10_Change()

End Sub
Private Sub TextBox11_Change()

End Sub
Private Sub TextBox12_Change()

End Sub
Private Sub TextBox13_Change()

End Sub
Private Sub TextBox14_Change()

End Sub
Private Sub TextBox15_Change()

End Sub
Private Sub TextBox16_Change()

End Sub
Private Sub TextBox17_Change()

End Sub
Private Sub TextBox18_Change()

End Sub
Private Sub TextBox19_Change()

End Sub
Private Sub TextBox2_Change()

End Sub
Private Sub TextBox20_Change()

End Sub
Private Sub TextBox21_Change()

End Sub
Private Sub TextBox22_Change()

End Sub
Private Sub TextBox23_Change()

End Sub
Private Sub TextBox3_Change()

End Sub
Private Sub TextBox4_Change()

End Sub
Private Sub TextBox5_Change()

End Sub
Private Sub TextBox6_Change()

End Sub
Private Sub TextBox7_Change()

End Sub
Private Sub TextBox8_Change()

End Sub
Private Sub TextBox9_Change()

End Sub

visual-basic-6

2 Contributors
9 Replies
134 Views
1 Week Discussion Span
Latest Post 13 Years Ago Latest Post by AndreRet

All 9 Replies

AndreRet 526 Senior Poster

13 Years Ago

Rather try to use the following -

'In a private sub the following...
Private Sub ClearAll(frm As Form)
Dim Control As Control

 For Each Control In frm.Controls
        If TypeOf Control Is TextBox Then
            Control.Text = vbNullString
        End If
       
    Next Control
End Sub

'Call event in your form activate
Call ClearAll Me 'Me or your form name..

Now use the same for your comboboxes

'Remember that you will use Combo.Clear
Private Sub ClearCombo(frm As Form)

Dim Control As Control

 For Each Control In frm.Controls
        If TypeOf Control Is ComboBox Then
            Control.Clear
        End If
       
    Next Control
End Sub

AndreRet 526 Senior Poster

13 Years Ago

Second, please excuse my ignorance but as I stated I am not a programmer and learning on the fly (time to enroll in a class).

We all had to start somewhere, trust me. Once bitten, you never let go...

Do I copy and paste your suggestion/solution EXACTLY as you have it posted or do I need to substitute my form name (EnrollmentForm) and control names (e.g. TextBox1, ComboBox1, CreditCard1 etc) in certain spots of the code?
Also in the code your comment states; 'Call event in your form activate'. Does that mean that I replace all that is currently in the Private Sub EnrollmentForm_Activate() and replace with Call ClearAll Me 'Me or your form name..?

At the top of your enrollment form, do the following - (This can also be done inside a module, just change the Private part to Public)

Option Explicit

Private Sub ClearAll(frm As Form)
Dim Control As Control
 
 For Each Control In frm.Controls
        If TypeOf Control Is TextBox Then
            Control.Text = vbNullString
        End If
 
    Next Control
End Sub

Private Sub ClearCombo(frm As Form)
 
Dim Control As Control
 
 For Each Control In frm.Controls
        If TypeOf Control Is ComboBox Then
            Control.Clear
        End If
 
    Next Control
End Sub

Private Sub EnrollmentForm_Load()

Call ClearAll Me
Call ClearCombo Me
End Sub

Copy and paste the code into your enrollment form

Delete the EnrollmentForm_Activate part completely

Leave all your other code as is

I'm sure this will help.:cool:

AndreRet 526 Senior Poster

13 Years Ago

DC, I do apologize for not seeing this earlier, but it seems you are using vb.Net? This will change the coding completely. Are you? Are you on vb6, .net or plain vb word?

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

dc_fdl 0 Newbie Poster · Answer 1 · 2010-09-21T20:02:41+00:00

AndreRet,

First of all THANK YOU for your reply and help.

Second, please excuse my ignorance but as I stated I am not a programmer and learning on the fly (time to enroll in a class).

Do I copy and paste your suggestion/solution EXACTLY as you have it posted or do I need to substitute my form name (EnrollmentForm) and control names (e.g. TextBox1, ComboBox1, CreditCard1 etc) in certain spots of the code?

Also in the code your comment states; 'Call event in your form activate'. Does that mean that I replace all that is currently in the Private Sub EnrollmentForm_Activate() and replace with Call ClearAll Me 'Me or your form name..?

I will experiment while awaiting a reply and if I am lucky enough to figure it out will reply as successful.

Thanks again...

dc_fdl 0 Newbie Poster · Answer 2 · 2010-09-23T02:00:16+00:00

Hello AndreRet,

Still having problems.

Again MANY THANKS for trying to help me and I hope that you don't tire of what is turning out to be many problems.

First of all if I copy and paste as you stated I can not even get past the first line with an F5 or compile.

Private Sub ClearAll(frm As Form) gets a compile error user defined type not defined and the same thing with Private Sub ClearCombo(frm As Form).

When I get to the Private Sub EnrollmentForm_Load() the [Call ClearAll Me and Call ClearCombo Me] turns red and I get a compile error.

If I edit as you see below, when I compile I continue to get a compile error variable not defined in the Public Sub [CommandButton1_Click()] [.Importance = olImportanceNormal] line.

Here is my code as it sits now:

Option Explicit
Public Sub ClearAll_EnrollmentForm()

Dim Control As Control

For Each Control In Enrollmentform
    If TypeOf Control Is TextBox Then
    Control.text = vbNullString
    End If
    
    Next Control
End Sub
Public Sub ClearCombo_EnrollmentForm()

Dim Control As Control

For Each Control In Enrollmentform
    If TypeOf Control Is ComboBox Then
    Control.Clear
    End If
    
    Next Control
End Sub
Public Sub EnrollmentForm_Load()

    ClearAll_EnrollmentForm
    ClearCombo_EnrollmentForm
            
End Sub
Private Sub ComboBox1_DropButtonClick()

Me.ComboBox1.List = Split("PROGRAMMING_LIST Fanuc_Horizontal_Milling_March14-18 Fanuc_Horizontal_Milling_June20-24 Fanuc_Horizontal_Milling_September12-16 Fanuc_Horizontal_Milling_December5-9 Fanuc_Vertical_Turning_January_31-February_4 Fanuc_Vertical_Turning_June_20-24 Fanuc_Vertical_Turning_September_12-16 Fanuc_Vertical_Turning_December_5-9 Siemens_840D_Standard_Programming_Horizontal_Milling_March_21-25 Siemens_840D_Standard_Programming_Horizontal_Milling_June_27-July_1 Siemens_840D_Standard_Programming_Horizontal_Milling_September_26-30 Siemens_840D_Standard_Programming_Horizontal_Milling_December_12-16 Siemens_840D_Standard_Programming_Vertical_Turning_February_21-25 Siemens_840D_Standard_Programming_Vertical_Turning_May_23-27 Siemens_840D_Standard_Programming_Vertical_Turning_August_15-19 Siemens_840D_Standard_Programming_Vertical_Turning_November_28-December_2 Siemens_840D_Advanced_Programming_All_Machines_March_1-4 Siemens_840D_Advanced_Programming_All_Machines_September_20-23 SPECIAL_REQUEST")



End Sub
Private Sub ComboBox2_DropButtonClick()

Me.ComboBox2.List = Split("MECHANICAL_LIST Mechanical_HBM_480/485_January_24-28 Mechanical_HBM_480/485_April_25-29 Mechanical_HBM_480/485_July_18-22 Mechanical_HBM_480/485_October_31-November_4 Mechanical_HBM_486_Consult_Factory Mechanical_HMC_568_February_14-18 Mechanical_VTC_524/525/526_March_7-11 Mechanical_VTC_524/525/526_June_6-10 Mechanical_VTC_524/525/526_September_12-16 Mechanical_VTC_524/525/526_December_5-9 Mechanical_VTC_523_Consult_Factory SPECIAL_REQUEST")



End Sub
Private Sub ComboBox3_DropButtonClick()

Me.ComboBox3.List = Split("ELECTRICAL_LIST Electrical_Fanuc_310i_January_17-21 Electrical_Fanuc_310i_April_4-8 Electrical_Fanuc_310i_July_11-15 Electrical_Fanuc_310i_October_3-7 Electrical_Siemens_840D_February_7-11 Electrical_Siemens_840D_May_2-6 Electrical_Siemens_840D_August_1-5 Electrical_Siemens_840D_November_7-11 SPECIAL_REQUEST")



End Sub
Public Sub CommandButton1_Click()
Dim OL As Object
    Dim EmailItem As Object
    Dim Doc As Document
    Application.ScreenUpdating = False
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem()
    Set Doc = ActiveDocument
    Doc.Save
        With EmailItem
        .Subject = "MAG Fond du Lac Training Enrollment"
        .Body = "Training Administrator," & vbCrLf & vbCrLf & _
        "Please enroll our employee in your customer training class per the attached form" & vbCrLf & vbCrLf & _
        "Thank You"
        .To = "engineeringtrain_do@mag-ias.com"
        .Importance = olImportanceNormal
        .Attachments.Add Doc.FullName
        .Display
         ' .Send
    End With
     
    Application.ScreenUpdating = True
    Set Doc = Nothing
    Set OL = Nothing
    Set EmailItem = Nothing
    End Sub
Private Sub CreditCard1_Click()

End Sub
Private Sub CreditCard2_Click()

End Sub
Private Sub CreditCard3_Click()

End Sub
Private Sub CreditCard4_Click()

End Sub
Private Sub TextBox1_Change()

End Sub
Private Sub TextBox10_Change()

End Sub
Private Sub TextBox11_Change()

End Sub
Private Sub TextBox12_Change()

End Sub
Private Sub TextBox13_Change()

End Sub
Private Sub TextBox14_Change()

End Sub
Private Sub TextBox15_Change()

End Sub
Private Sub TextBox16_Change()

End Sub
Private Sub TextBox17_Change()

End Sub
Private Sub TextBox18_Change()

End Sub
Private Sub TextBox19_Change()

End Sub
Private Sub TextBox2_Change()

End Sub
Private Sub TextBox20_Change()

End Sub
Private Sub TextBox21_Change()

End Sub
Private Sub TextBox22_Change()

End Sub
Private Sub TextBox23_Change()

End Sub
Private Sub TextBox3_Change()

End Sub
Private Sub TextBox4_Change()

End Sub
Private Sub TextBox5_Change()

End Sub
Private Sub TextBox6_Change()

End Sub
Private Sub TextBox7_Change()

End Sub
Private Sub TextBox8_Change()

End Sub
Private Sub TextBox9_Change()

End Sub

dc_fdl 0 Newbie Poster · Answer 3 · 2010-09-23T03:26:51+00:00

Andre

Sorry I thought (at least I intended) that I mentioned that all I have is (what I believe to be) VBA 6.5.

All I have is whatever opens with Office 2003; no full version of VB at all.

AndreRet 526 Senior Poster · Answer 4 · 2010-09-23T15:44:09+00:00

No, this was my fault, did not read properly. So, we will have to change the code a little bit -

'In your form_Activate sub, paste the following...
For Each ctl As Control In Controls

   If TypeOf ctl Is TextBox Then

      CType(ctl, TextBox).Clear()

   End If

Next ctl

This hopefully solves your problem...

dc_fdl 0 Newbie Poster · Answer 5 · 2010-09-28T00:59:10+00:00

AndreRet,

Thank you very much for your attempts to help solve my problem. However the code that you have sent still does not work. I think I will have to turn this over to someone else within our organization and see if they can find a solution. As much as I would like to see this through I can ill-afford any more time on this issue.

Again many thanks for you assistance.

AndreRet 526 Senior Poster · Answer 6 · 2010-09-28T14:35:09+00:00

I am sorry you still could not get this to work. I went and searched some solutions. Check out the following links, you might get your answer there -

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=362
Or,
http://www.excelforum.com/excel-programming/675587-commandbutton-to-clear-textboxes-in-vba.html
Or,
http://social.answers.microsoft.com/Forums/en-US/addbuz/thread/741c37e9-e79b-45d3-9e6f-53045308fd18
Or,
http://www.computing.net/answers/programming/clearing-a-textbox-vb6/7686.html

I hope you can find a solution in there if time permitted.

Word 2003 doc/template to form VB(A?) 6.5

Recommended Answers Collapse Answers

All 9 Replies

Recommended Answers