VBA USERFORM for MSWord

[WendyHubard]

Hello Guru's,

I've done something really dumb with one of my macro enabled templates. The userform appears and allows the user to enter the data, afterc licking the OK button, the word document populates, but the userform refreshes instead of hiding/closing/disappearing. If I select the OK button again, the userform will close. Any clues on what silly thing I have done?

I'm not sure what to include. Have just copied the VBA code....

Many thanks in advance.

Private Sub CommandButton1_Click()

Dim Complaint As Range
Set Complaint = ActiveDocument.Bookmarks("Complaint").Range
Complaint.Text = Me.TextBox1.Value

Dim File As Range
Set File = ActiveDocument.Bookmarks("File").Range
File.Text = Me.TextBox2.Value

Dim Accused As Range
Set Accused = ActiveDocument.Bookmarks("Accused").Range
Accused.Text = Me.TextBox3.Value

Dim Charges As Range
Set Charges = ActiveDocument.Bookmarks("Charges").Range
Charges.Text = Me.TextBox4.Value

Dim Accused2 As Range
Set Accused = ActiveDocument.Bookmarks("Accused2").Range
Accused.Text = Me.TextBox3.Value

Dim Complaint2 As Range
Set Complaint = ActiveDocument.Bookmarks("Complaint2").Range
Complaint.Text = Me.TextBox1.Value

Dim Charges2 As Range
Set Charges = ActiveDocument.Bookmarks("Charges2").Range
Charges.Text = Me.TextBox4.Value

Dim DateAppeared As Range
Set DateAppeared = ActiveDocument.Bookmarks("DateAppeared").Range
DateAppeared.Text = Me.TextBox5.Value

Dim DateAppeared2 As Range
Set DateAppeared = ActiveDocument.Bookmarks("DateAppeared2").Range
DateAppeared.Text = Me.TextBox5.Value

Dim Magistrate As Range
Set Magistrate = ActiveDocument.Bookmarks("Magistrate").Range
Magistrate.Text = Me.ComboBox1.Value

Dim Magistrates As Range
Set Magistrate = ActiveDocument.Bookmarks("Magistrate2").Range
Magistrate.Text = Me.ComboBox1.Value

Dim Court As Range
Set Court = ActiveDocument.Bookmarks("Court").Range
Court.Text = Me.ComboBox2.Value

Dim Court2 As Range
Set Court = ActiveDocument.Bookmarks("Court2").Range
Court.Text = Me.ComboBox2.Value

Dim Solicitor As Range
Set Solicitor = ActiveDocument.Bookmarks("Solicitor").Range
Solicitor.Text = Me.TextBox6.Value

Dim Solicitor2 As Range
Set Solicitor = ActiveDocument.Bookmarks("Solicitor2").Range
Solicitor.Text = Me.TextBox6.Value

Dim Firm As Range
Set Firm = ActiveDocument.Bookmarks("Firm").Range
Firm.Text = Me.TextBox7.Value

Dim Firm2 As Range
Set Firm = ActiveDocument.Bookmarks("Firm2").Range
Firm.Text = Me.TextBox7.Value

Dim Plea As Range
Set Plea = ActiveDocument.Bookmarks("Plea").Range
Plea.Text = Me.ComboBox3.Value

Dim Plea2 As Range
Set Plea = ActiveDocument.Bookmarks("Plea2").Range
Plea.Text = Me.ComboBox3.Value

Dim SupremeDate As Range
Set SupremeDate = ActiveDocument.Bookmarks("SupremeDate").Range
SupremeDate.Text = Me.TextBox8.Value

Dim SupremeDate2 As Range
Set SupremeDate = ActiveDocument.Bookmarks("SupremeDate2").Range
SupremeDate.Text = Me.TextBox8.Value

Dim outcome As Range
Set outcome = ActiveDocument.Bookmarks("Outcome").Range
outcome.Text = Me.ComboBox4.Value

Dim outcome2 As Range
Set outcome = ActiveDocument.Bookmarks("Outcome2").Range
outcome.Text = Me.ComboBox4.Value

Dim outcome3 As Range
Set outcome = ActiveDocument.Bookmarks("Outcome3").Range
outcome.Text = Me.ComboBox4.Value

Dim outcome4 As Range
Set outcome = ActiveDocument.Bookmarks("Outcome4").Range
outcome.Text = Me.ComboBox4.Value


'Me.Repaint
'UserForm1.Hide
'UserForm1.Close
Unload UserForm1
'Unload Me



End Sub

 

[Macropod]

The code itself looks OK, though it could be reduced to:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With ActiveDocument
  .Bookmarks("Complaint").Range.Text = Me.TextBox1.Value
  .Bookmarks("Complaint2").Range.Text = Me.TextBox1.Value
  .Bookmarks("File").Range.Text = Me.TextBox2.Value
  .Bookmarks("Accused").Range.Text = Me.TextBox3.Value
  .Bookmarks("Accused2").Range.Text = Me.TextBox3.Value
  .Bookmarks("Charges").Range.Text = Me.TextBox4.Value
  .Bookmarks("Charges2").Range.Text = Me.TextBox4.Value
  .Bookmarks("DateAppeared").Range.Text = Me.TextBox5.Value
  .Bookmarks("DateAppeared2").Range.Text = Me.TextBox5.Value
  .Bookmarks("Magistrate").Range.Text = Me.ComboBox1.Value
  .Bookmarks("Magistrate2").Range.Text = Me.ComboBox1.Value
  .Bookmarks("Court").Range.Text = Me.ComboBox2.Value
  .Bookmarks("Court2").Range.Text = Me.ComboBox2.Value
  .Bookmarks("Solicitor").Range.Text = Me.TextBox6.Value
  .Bookmarks("Solicitor2").Range.Text = Me.TextBox6.Value
  .Bookmarks("Firm").Range.Text = Me.TextBox7.Value
  .Bookmarks("Firm2").Range.Text = Me.TextBox7.Value
  .Bookmarks("Plea").Range.Text = Me.ComboBox3.Value
  .Bookmarks("Plea2").Range.Text = Me.ComboBox3.Value
  .Bookmarks("SupremeDate").Range.Text = Me.TextBox8.Value
  .Bookmarks("Outcome").Range.Text = Me.ComboBox4.Value
  .Bookmarks("Outcome2").Range.Text = Me.ComboBox4.Value
  .Bookmarks("Outcome3").Range.Text = Me.ComboBox4.Value
  .Bookmarks("Outcome4").Range.Text = Me.ComboBox4.Value
End With
Application.ScreenUpdating = True
Unload Me
End Sub

 

[WendyHubard]

Hey Paul,

Thanks heaps. I've used your code as it's a trillion times better than mine I still have the same issue, when i click on the OK button, it populates the document but refreshes the userform and leaves it displayed ready for input. Are you able to assist any further?
Kind regards

Wendy

 

[Macropod]

The problem isn't in the code you posted (or my revised version). Maybe there's a problem elsewhere (e.g. loading your userform twice). Do the variables get output twice?

 

[WendyHubard]

Hi Paul,
Good Grief. I don't know why I did it.... Have now fixed as per your comments above. I really really appreciate your help. It was driving me crazy!!!!

Sub AutoNew()
'run when file opens
UserForm1.Show
End Sub

Private Sub Document_New()
'UserForm1.Show

End Sub