streub
10-19-2013, 07:18 AM
I am having difficulty placing a message box with the option of yes or no to execute a sub. If the user selects yes the sub executes and if the user selects no the sub is aborted with a mess of activex controls being clear and hidden.
Is it better to use input boxes and if so, how do I use the activex controls?
Private Sub CommandButton3_Click()
Dim nullstring
If Me.ComboBox1.Value = "" Or nullstring Then
MsgBox "Selection Required"
Exit Sub
End If
If Len(Me.TextBox1.Text) < 3 Then
MsgBox "The Minimum of Three Alpha Characters Required"
TextBox1.Value = ""
Exit Sub
End If
MsgBox "THIS CHANGE IS PERMANENT!", vbYesNo
Dim WS As Worksheet
Set WS = Worksheets("combined")
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean
Search = ComboBox1.Value
Replacement = TextBox1.Value
For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next
Me.ComboBox1.Value = ""
Me.ComboBox1.Visible = False
TextBox1.Value = ""
TextBox1.Visible = False
TextBox2.Value = ""
CommandButton3.Visible = False
Range("h18:i18").ClearContents
MsgBox "Change Completed"
End Sub
Is it better to use input boxes and if so, how do I use the activex controls?
Private Sub CommandButton3_Click()
Dim nullstring
If Me.ComboBox1.Value = "" Or nullstring Then
MsgBox "Selection Required"
Exit Sub
End If
If Len(Me.TextBox1.Text) < 3 Then
MsgBox "The Minimum of Three Alpha Characters Required"
TextBox1.Value = ""
Exit Sub
End If
MsgBox "THIS CHANGE IS PERMANENT!", vbYesNo
Dim WS As Worksheet
Set WS = Worksheets("combined")
Dim Search As String
Dim Replacement As String
Dim Prompt As String
Dim Title As String
Dim MatchCase As Boolean
Search = ComboBox1.Value
Replacement = TextBox1.Value
For Each WS In Worksheets
WS.Cells.Replace What:=Search, Replacement:=Replacement, _
LookAt:=xlPart, MatchCase:=False
Next
Me.ComboBox1.Value = ""
Me.ComboBox1.Visible = False
TextBox1.Value = ""
TextBox1.Visible = False
TextBox2.Value = ""
CommandButton3.Visible = False
Range("h18:i18").ClearContents
MsgBox "Change Completed"
End Sub