VBA Macro for Single Button w/different Message Results

[lossi44]

Good morning all,

I am new to VBA, had a single class a few days ago. We have an assignment and I'd like to ask for some help to review and wrap my head around it. Below is the module I started, but obviously needs some help. Any tips on sequence and how to make it work would greatly be appreciated.

Best regards,

Carlos


Assignment Description:
Create a simple Excel workbook and VBA macro in which a user is provided a single button to click. Based on the number they provide in a text box above, a different message box will appear.

I know it's not correct, but from my notes I believe I will use something like the following:


----------------------------------------------------------------------
Sub FiveMessages()

Cells(2, 1).Value

' Retrieve the user sentence and store in variable
Dim Sentence As String
Sentence = Cells(1, 2).Value
MsgBox (Sentence)

' Retrieve the user word numbers and store in variables
Dim num1 As Integer
Dim num2 As Integer
Dim num3 As Integer
Dim num4 As Integer
Dim num5 As Integer

num1 = Cells(1, 1).Value
num2 = Cells(1, 2).Value
num3 = Cells(1, 3).Value
num4 = Cells(1, 4).Value
num5 = Cells(1, 5).Value

MsgBox (num1), ("This is the first option.")
MsgBox (num2), ("This is the second option.")
MsgBox (num3), ("This is the third option.")
MsgBox (num4), ("This is the fourth option.")
MsgBox (num5), ("This is the fifth option.")


End Sub

 

[DanteAmor]

Re: Help w/VBA Macro for Single Button w/different Message Results

Try this

Sub FiveMessages()
    Dim num As Variant
    num = Range("A2").Value
    
    Select Case num
        Case 1: MsgBox num & " This is the first option."
        Case 2: MsgBox num & " This is the second option."
        Case 3: MsgBox num & " This is the third option."
        Case 4: MsgBox num & " This is the fourth option."
        Case 5: MsgBox num & " This is the fifth option."
    End Select
End Sub

 

[Steve_]

Re: Help w/VBA Macro for Single Button w/different Message Results

Try this

Sub FiveMessages()
    Dim num As Variant
    num = Range("A2").Value
    
    Select Case num
        Case 1: MsgBox num & " This is the first option."
        Case 2: MsgBox num & " This is the second option."
        Case 3: MsgBox num & " This is the third option."
        Case 4: MsgBox num & " This is the fourth option."
        Case 5: MsgBox num & " This is the fifth option."
    End Select
End Sub

I have updated DanteAmor's code to include the assignment requirements of a textbox and a command button.

Private Sub CommandButton1_Click()
    If Not IsNumeric(TextBox1.Text) Then Exit Sub
    Select Case CInt(TextBox1.Text)
        Case 1
            MsgBox "Response 1.", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
        Case 2
            MsgBox "Response 2.", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
        Case 3
            MsgBox "Response 3.", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
        Case 4
            MsgBox "Response 4.", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
        Case 5
            MsgBox "Response 5.", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
        Case Else
            MsgBox "Invalid Number Entered. Please choose a number between 1 and 5", vbOKOnly + vbInformation, "Value from the Textbox is: " & TextBox1.Text
    End Select
End Sub

"Assignment Description:
Create a simple Excel workbook and VBA macro in which a user is provided a single button to click. Based on the number they provide in a text box above, a different message box will appear.

I know it's not correct, but from my notes I believe I will use something like the following:"

*********************

Based on that description, it is my assumption that you will need to add a CommandButton and a Textbox to a sheet and add this code into the commandbutton

 

[lossi44]

Re: Help w/VBA Macro for Single Button w/different Message Results

Dante,

Muchas gracias, it worked! Is there a way to enter the resulting text in a cell rather than a pop-up message box?

Best,

Carlos

 

[lossi44]

Re: Help w/VBA Macro for Single Button w/different Message Results

Steve, that is awesome, thank you very much. That helps with the described assignment. I really appreciate it! Best Regards, Carlos

 

[DanteAmor]

Re: Help w/VBA Macro for Single Button w/different Message Results

Dante,

Muchas gracias, it worked! Is there a way to enter the resulting text in a cell rather than a pop-up message box?

Best,

Carlos

Try

Sub FiveMessages()
    Dim num As Variant
    num = Range("A2").Value
    
    Select Case num
        Case 1: Range("B2").Value = num & " This is the first option."
        Case 2: Range("B2").Value =  num & " This is the second option."
        Case 3: Range("B2").Value = num & " This is the third option."
        Case 4: Range("B2").Value =  num & " This is the fourth option."
        Case 5: Range("B2").Value = num & " This is the fifth option."
    End Select
End Sub