Message Box User Input in VBA (I Always Forget This)

VBA isn't know for it's user interface ability, but sometimes it's nice to capture the user input from a message box. I believe that all buttons in a message box have values, but I typically use this when I offer the user a Yes/No option (i.e. "This is going to do something that may take some time. Do you want to continue?")

Simple Example (Excel 2010):

Dim userInput as Integer
userInput = MsgBox("What do you choose?", vbYesNo, "User Choice")
If userInput = 6 Then '6 = Yes
'Enter "Yes" code
ElseIf userInput = 7 Then '7 = No
'Enter "No" code
End If

A couple of notes. When you want to capture the selection from a message box you do need to use parenthesis around the message box properties. The integer values from the Yes/No responses may be different values based on the version of Excel being used. An easy way to check is to throw a break point right after assigning a value to the userInput variable.

Select Case in VBA

I keep forgetting to add things as I build my new project so I am going to make an attempt to add these as I use them. The posts will be smaller, and each will focus on a single item.

Select Case
Case
Case
...
Case Else
End Select

Very Simple Example:

Dim myval as string
myval = ActiveCell.Value
Select Case myval
Case "Blue"
MsgBox "Cell value is Blue"
Case "Red"
MsgBox "Cell value is Red"
Case "Green"
MsgBox "Cell value is Green"
Case Else
MsgBox "Cell value not recognized
End Select

Pulling An Excel Column Letter in VBA

The formula below will return just the column letter based on the active cell. This can be modified to return the column letter for any referenced cell. There are many ways to do this, but this was one of the more elegant solutions I came across in my search.

ColumnID = Mid(ActiveCelll.Address, 2, InStr(2, ActiveCell.Address, "$") - 2)