There are some deliberate mistakes and some overkill in your code. Note Option Explicit only ever goes (once) at the top of the module and is there to force you to declare variables used in the code. You can set it automatically in new modules from the VBA editor Tools > Options.
I have added a function from my web site to fill bookmarks rather than set them separately.
I have no idea what you are trying to achieve with the CommandButton1, Copy and Reset buttons. You can set initial values of controls with the UserForm_initialize sub. The code should be in a template from which new documents are created.
Option Explicit
'Cancel button
Private Sub CancelBut_Click()
Unload Me
End Sub
'Reset button
Private Sub ResetBut_Click()
Dim ctl As MSForms.Control
For Each ctl In Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.value = True
Case "ComboBox", "ListBox"
ctl.ListIndex = 0
End Select
Next ctl
End Sub
'Copy button
'What is this supposed to achieve?
Private Sub CopyButton1_Click()
Selection.WholeStory 'Select whole document
Selection.Copy 'Copy the selection
Dim ctl As MSForms.Control
For Each ctl In Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.value = True
Case "ComboBox", "ListBox"
ctl.ListIndex = 0
End Select
Next ctl
UserForm1.Hide
End Sub
Private Sub OptionButton1_Change()
If OptionButton1.value = True Then
TextBox2.Visible = False
TextBox2.Text = "None."
Else
TextBox2.Visible = True
TextBox2.Text = ""
End If
End Sub
Private Sub CommandButton1_Click() 'Why this button?
Dim oRng As Range
If ActiveDocument.Bookmarks.Exists("Other") = True Then
Set oRng = ActiveDocument.Bookmarks("Other").Range
oRng.Text = TextBox2.Text
oRng.Bookmarks.Add "Other"
End If
Unload Me
End Sub
'Enter button
'Option Explicit 'This only ever goes at the top of the module
Private Sub EnterBut_Click()
Dim oThreat As Range, oHarm As Range, oOpportunity As Range
Dim oRisk As Range, oDepartment As Range
'check required fields are filled first ... what about checking that the text boxes are filled also?
If ComboBox1.ListIndex = 0 Then
MsgBox "Select threat"
ComboBox1.SetFocus
Exit Sub
End If
If ComboBox2.ListIndex = 0 Then
MsgBox "Select harm"
ComboBox2.SetFocus
Exit Sub
End If
If ComboBox3.ListIndex = 0 Then
MsgBox "Select opportunity"
ComboBox3.SetFocus
Exit Sub
End If
If ComboBox4.ListIndex = 0 Then
MsgBox "Select risk"
ComboBox4.SetFocus
Exit Sub
End If
If ComboBox5.ListIndex = 0 Then
MsgBox "Select department"
ComboBox5.SetFocus
Exit Sub
End If
'use FillBM function to write to bookmarks
FillBM "Occurrence", TextBox1.Text
FillBM "Other", TextBox2.Text
FillBM "Research", TextBox3.Text
FillBM "Threat1", TextBox4.Text
FillBM "Harm1", TextBox5.Text
FillBM "Opportunity", TextBox6.Text
FillBM "Risk1", TextBox7.Text
FillBM "Department1", TextBox8.Text
'Or process the bookmarks individually
If ActiveDocument.Bookmarks.Exists("Threat") = True Then
Set oThreat = ActiveDocument.Bookmarks("Threat").Range
oThreat.Text = ComboBox1.value
oThreat.Font.Color = ComboBox1.BackColor
ActiveDocument.Bookmarks.Add "Threat", oThreat
End If
If ActiveDocument.Bookmarks.Exists("Harm") = True Then
Set oHarm = ActiveDocument.Bookmarks("Harm").Range
oHarm.Text = ComboBox2.value
oHarm.Font.Color = ComboBox2.BackColor
ActiveDocument.Bookmarks.Add "Harm", oHarm
End If
If ActiveDocument.Bookmarks.Exists("Opportunity") = True Then
Set oOpportunity = ActiveDocument.Bookmarks("Opportunity").Range
oOpportunity.Text = ComboBox3.value
oOpportunity.Font.Color = ComboBox3.BackColor
ActiveDocument.Bookmarks.Add "Opportunity", oOpportunity
End If
If ActiveDocument.Bookmarks.Exists("Risk") = True Then
Set oRisk = ActiveDocument.Bookmarks("Risk").Range
oRisk.Text = ComboBox4.value
oRisk.Font.Color = ComboBox4.BackColor
ActiveDocument.Bookmarks.Add "Risk", oRisk
End If
If ActiveDocument.Bookmarks.Exists("Department") = True Then
Set oDepartment = ActiveDocument.Bookmarks("Department").Range
oDepartment.Text = ComboBox5.value
ActiveDocument.Bookmarks.Add "Department", oDepartment
End If
Set oThreat = Nothing
Set oHarm = Nothing
Set oOpportunity = Nothing
Set oRisk = Nothing
Set oDepartment = Nothing
Unload Me
lbl_Exit:
Exit Sub
End Sub
Private Sub ComboBox1_Change()
With ComboBox1
Select Case .ListIndex
Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
End Select
End With
lbl_Exit:
Exit Sub
End Sub
Private Sub ComboBox2_Change()
With ComboBox2
Select Case .ListIndex
Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
End Select
End With
lbl_Exit:
Exit Sub
End Sub
Private Sub ComboBox3_Change()
With ComboBox3
Select Case .ListIndex
Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
End Select
End With
lbl_Exit:
Exit Sub
End Sub
Private Sub ComboBox4_Change()
With ComboBox4
Select Case .ListIndex
Case 0: .BackColor = &H80000005: .ForeColor = &H80000008
Case 1: .BackColor = &HFF&: .ForeColor = &H80000005
Case 2: .BackColor = &H80FF&: .ForeColor = &H80000005
Case 3: .BackColor = &H8000&: .ForeColor = &H80000005
End Select
End With
lbl_Exit:
Exit Sub
End Sub
Private Sub UserForm_initialize()
Dim myArray() As String
'Create list of grades for threat
myArray = Split("- Select -|High|Medium|Low", "|")
'Use List method to populate listbox
ComboBox1.List = myArray
ComboBox1.ListIndex = 0
'Use List method to populate listbox
ComboBox2.List = myArray
ComboBox2.ListIndex = 0
'Use List method to populate listbox
ComboBox3.List = myArray
ComboBox3.ListIndex = 0
'Use List method to populate listbox
ComboBox4.List = myArray
ComboBox4.ListIndex = 0
'Redefine list for departments
myArray = Split("- Select -|Resolution Centre|Local|PPN1|Amberstone|Collision Assessment", "|")
'Use List method to populate listbox
ComboBox5.List = myArray
ComboBox5.ListIndex = 0
OptionButton1.value = True
lbl_Exit:
Exit Sub
End Sub
Public Sub FillBM(strbmName As String, strValue As String)
'Graham Mayor - http://www.gmayor.com
Dim oRng As Range
With ActiveDocument
On Error GoTo lbl_Exit
If .Bookmarks.Exists(strbmName) = True Then
Set oRng = .Bookmarks(strbmName).Range
oRng.Text = strValue
oRng.Bookmarks.Add strbmName
End If
End With
lbl_Exit:
Set oRng = Nothing
Exit Sub
End Sub