Conditional MsgBox

**DJvdW** · 09-11-2013, 06:11 PM

I have created a UserForm with 5 ComboBoxes and 1 TextBox. For the ComboBoxes, the Enabled property is set to False and the Locked property is set to True. On initializing the UserForm, these properties on some of the ComboBoxes are changed based on the value of a cells in the worksheet. The user can now change the values in only the unlocked ComboBoxes and the TextBox.

I want to display the new values of only the ComboBoxes that are unlocked and the TextBox, so that the user can confirm their entries.

The message displayed should look like this:

The new values are:
ComboBox1 - Value1
ComboBox2 - Value2
TextBox - Value3

I can write the MsgBox statement so that the message is displayed as above, but all ComboBoxes are listed.

I would like to now if it is possible to display only the qualifying ComboBoxes in a MsgBox, and what the syntax MsgBox statement should be.

Any help would be appreciated.

**tehneXus** · 09-11-2013, 07:02 PM

Hi,

you can loop the comboboxes and construct a string of values first, then displaying the msgbox, e.g.:

Private Sub CommandButton1_Click()
    
    Dim ctrl As Control
    Dim strMsg As String
    
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "ComboBox" Then
            If ctrl.Enabled Then
                If strMsg = vbNullString Then
                    strMsg = ctrl.Name & ": " & ctrl.Value
                Else
                    strMsg = strMsg & vbNewLine & ctrl.Name & ": " & ctrl.Value
                End If
            End If
        End If
    Next ctrl
    Select Case MsgBox("Check the values: " & vbNewLine & strMsg & vbNewLine & "Are these fine?", vbQuestion + vbYesNo, "Confirm")
        Case vbNo
            'do sth
        Case vbYes
            'do sth else
    End Select
End Sub

This is looping all controls on a UF, if its a combobox and its enabled the content and name is stored in a string

**DJvdW** · 09-12-2013, 08:07 AM

Thank you tehneXus

This is exactly what I needed.

However, the name of the ComboBox (e.g. ExpCat1ComboBox) is not very user friendly. Is there a way to change the Name or Caption of the ComboBox, and displaying the new Name or Caption. I have done this for the Label associated with the ComboBox, but do not know how to do it for the ComboBox.

Thank you.

**Andy Pope** · 09-12-2013, 08:49 AM

Simplest thing would be to use the comboboxes Tag property to store desired output text

**DJvdW** · 09-13-2013, 08:24 AM

Thank you Andy and tehneXus

I have changed tehneXus' code to use the .Tag property and not the .Name property (see the changed code below):

strMsg = "You are about to change the Expense Category Codes and/or the Reference Number." & vbCr & vbCr _
& "The New values are:" & vbCr

For Each ctrl In Me.Controls
  
  If TypeName(ctrl) = "ComboBox" Then
    If ctrl.Enabled Then
        strMsg = strMsg & vbCr & ctrl.Tag & " :" & Space(40 - Len(ctrl.Tag)) & ctrl.Value
    End If
  End If
Next ctrl

Confirm = MsgBox(strMsg & vbCr _
& RefNoLabel & " :" & Space(40 - Len(RefNoLabel)) & RefNoTextBox.Value & vbCr & vbCr _
& "Is this correct?", vbYesNo, "Confirm Change")

I am setting the Tag property when the form is Initialized, i.e.

Me.ExpCat1ComboBox.Tag = Sheet3.Range("B10").Value

Thank you for your help.

Conditional MsgBox

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Conditional MsgBox

Re: Conditional MsgBox

Re: Conditional MsgBox

Re: Conditional MsgBox

Re: Conditional MsgBox

Thread Information

Users Browsing this Thread

Similar Threads

Msgbox

MsgBox-center the message on a msgbox

MsgBox-at the end a msgBox popup

MsgBox Help

[SOLVED] Msgbox

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions