We help IT Professionals succeed at work.

Excel VBA: send specific information based on values defined in a UserForm/ComboBox

Hello experts,

I am looking for a procedure that allows me to define specific values in a ComboBox and based on the value selected send information for range B1, B2, B3, B4, B5.
The idea is to have Dev, Qual, Prod values in the ComoBox and based on this send information for the ranges listed bellow.
I prefer to manage values in an Array instead of defining specific range at worksheet level.
I attached dummy file in which I have already defined UserForm.
20191102_145820-screenshot.pngIf you have questions, please contact me.
Thank you for your help.
UserForm-ComboBox.xlsm
Comment
Watch Question

President / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018
Commented:
Wht about something like
Private Sub ComboBox1_Change()
    Select Case Me.ComboBox1
    Case "Dev"
        Sheets("Config").Range("B1").Value = ""
        Sheets("Config").Range("B2").Value = ""
        Sheets("Config").Range("B3").Value = ""
        Sheets("Config").Range("B4").Value = ""
        Sheets("Config").Range("B5").Value = ""
    Case "Qual"
        Sheets("Config").Range("B1").Value = ""
        Sheets("Config").Range("B2").Value = ""
        Sheets("Config").Range("B3").Value = ""
        Sheets("Config").Range("B4").Value = ""
        Sheets("Config").Range("B5").Value = ""
    Case "Prod"
        Sheets("Config").Range("B1").Value = ""
        Sheets("Config").Range("B2").Value = ""
        Sheets("Config").Range("B3").Value = ""
        Sheets("Config").Range("B4").Value = ""
        Sheets("Config").Range("B5").Value = ""
    End Select
End Sub

Open in new window


You could used named ranges if you want, an array, ... whatever tickles your fancy.
Peter ChanProblem resolver
BRONZE EXPERT

Commented:
You can choose
Data > Data Validation

to define a dynamic list for relevant Cell like

4d.png
Luis DiazIT consultant

Author

Commented:
Thank you.
@Daniel Pineault:
Why not defining
with Worksheets("") end with in between the select cases?
Instead of adding multiple times the Sheet("Config") statement multiple times?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
BRONZE EXPERT
Distinguished Expert 2018

Commented:
Why not defining with Worksheets("") end with in between the select cases?
No particular reason, you could easily do so if you like.
Luis DiazIT consultant

Author

Commented:
I reviewed like this and it works:

'User form
Private Sub UserForm_Activate()
    With ComboBox1
        .AddItem ("Dev")
        .AddItem ("Qual")
        .AddItem ("Prod")
    End With
End Sub


'Combo box
Private Sub ComboBox1_Change()
    Dim strWS As Worksheet
    
    Set strWS = Worksheets("Config")
    
    Select Case Me.ComboBox1
        Case "Dev"
            With strWS
                'Server
                .Range("B1").Value = "Server"
                'User
                .Range("B2").Value = "User"
                'Password
                .Range("B3").Value = "Password"
                'Port
                .Range("B4").Value = "Port"
                'Database
                .Range("B5").Value = "Database"
            End With
        Case "Qual"
            With strWS
                'Server
                .Range("B1").Value = ""
                'User
                .Range("B2").Value = ""
                'Password
                .Range("B3").Value = ""
                'Port
                .Range("B4").Value = ""
                'Database
                .Range("B5").Value = ""
            End With
        Case "Prod"
            With strWS
                'Server
                .Range("B1").Value = ""
                'User
                .Range("B2").Value = ""
                'Password
                .Range("B3").Value = ""
                'Port
                .Range("B4").Value = ""
                'Database
                .Range("B5").Value = ""
            End With
    End Select
End Sub


'Close button
Private Sub CloseUserForm_Click()
Unload Menu
End Sub

Open in new window


Just one remark. If I want to send information with an Upload button instead of doing it after the selection in the ComboBox how should I proceed?