PDA

View Full Version : [SOLVED] Message Box using vbYesNo



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

snb
10-19-2013, 07:27 AM
Private Sub combobox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

Private Sub TextBox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

streub
10-19-2013, 08:21 AM
Please forgive my ignorance but how does this address the yes/no message box issue?




Private Sub combobox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

Private Sub TextBox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

snb
10-19-2013, 09:21 AM
It makes the message box unnecessary/redundant.
It also simplifies your code.

You should inform the user what is being expected beforehand, not afterwards.
As long as commandbutton3 isn't visible the user stick in the same screen.
If she wants to continue she has to fill the textbox and the combobox correctly first.

streub
10-19-2013, 11:15 AM
Well stated; however you are sexist. Not all my users are females.

Thank you snb!

snb
10-19-2013, 02:10 PM
My approach is gender independent. Please test it and report the results, :)

streub
10-19-2013, 04:35 PM
My approach is gender independent. Please test it and report the results, :)

"Gender Independent", that's a first for me. I will apply and test when I have less distractions (customers).

Paul_Hossler
10-19-2013, 06:29 PM
Did not really follow the discussion, but if you were asking about



MsgBox "THIS CHANGE IS PERMANENT!", vbYesNo




then I think a better way to get a response is to use the function form of Msgbox and do something like ...



Option Explicit
Sub demo()
Const sModule As String = "Demo of YesNo"

If MsgBox("THIS CHANGE IS PERMANENT!", vbQuestion + vbYesNo + vbDefaultButton2, sModule) = vbYes Then
Call MsgBox("OK, if you say so", vbExclamation + vbOKOnly, sModule)
Else
Call MsgBox("OK, then I won't", vbInformation + vbOKOnly, sModule)
End If
End Sub



Paul

streub
10-20-2013, 09:52 AM
I receive an error stating the command button (obeObject) cannot be found. The change does execute!



Private Sub combobox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

Private Sub TextBox1_change()
commandbutton3.visible=(combobox1.listindex>-1)*(len(TextBox1.Text)=3)
End Sub

snb
10-20-2013, 12:58 PM
Please post a workbook that only contains 1 textbox1, 1 combobox1 and 1 commandbutton3.
Like the attachment.

streub
10-21-2013, 02:35 PM
Thank you. I will give it a go.