Fairly easy code, so what's the problem?

[Bruiser616]

I have a spreadsheet that I would like to pop up an input box to prompt the user to give one of 4 values. I only want this to work for Column C and I would like the Input Box to pop up when a cell is clicked in (preferably the right-click button). Depending on the value the background shading will be changed.

Here is what I have so far. Part of the error handling is commented out for testing.

Sub ColorSupportType()

With Range("C2:D1")
BoxColor = InputBox("Enter S for Successful, P for Partial, F for Failed, or SH for Shadow. Click cancel if no change is needed.", "Support Success?")

'Error Handling
If BoxColor = " " Then 'Or Not "S" Or Not "P" Or Not "F" Or Not "SH" Then
MsgBox "Please enter S, P, F, or SH", "Invalid Entry"
End If

If BoxColor = "S" Then
ActiveCell.Interior.ColorIndex = 4
ElseIf BoxColor = "P" Then
ActiveCell.Interior.ColorIndex = 6
ElseIf BoxColor = "F" Then
ActiveCell.Interior.ColorIndex = 3
ElseIf BoxColor = "SH" Then
ActiveCell.Interior.ColorIndex = 15
End If
End With

End Sub

1. I specified the Range but it lets you use this from any Cell. I only want to use it for Column C cells.

2. I can't figure out to get the error checking to work. Is "Or Not" an invalid condition in VBA?

3. How do I get the right click popup to work?

Thanks!

 

[Scott Huish]

Hi, welcome to the board!

Try this in the Worksheet BeforeRightClick event:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("C2:D1")) Is Nothing Then Exit Sub
Dim BoxColor
GetEntry:
BoxColor = UCase(InputBox("Enter S for Successful, P for Partial, F for Failed, or SH for Shadow. Click cancel if no change is needed.", "Support Success?"))
If BoxColor = "" Then
    Cancel = True
    Exit Sub
End If
Select Case BoxColor
    Case "S"
        ActiveCell.Interior.ColorIndex = 4
    Case "P"
        ActiveCell.Interior.ColorIndex = 6
    Case "F"
        ActiveCell.Interior.ColorIndex = 3
    Case "SH"
        ActiveCell.Interior.ColorIndex = 15
    Case Else
        MsgBox "Please enter S, P, F, or SH", vbOKOnly, "Invalid Entry"
        GoTo GetEntry
End Select
Cancel = True
End Sub

 

[HalfAce]

Hello & welcome to the board.
You're right in that "Or Not "S" Or Not "P" Or Not "F" Or Not "SH" Then" is incorrect syntax. We could write that a couple of different ways, such as:
If BoxColor <> "S" And BoxColor <> "P" (etc.) Then
or such as:
If Not BoxColor = "S" Or BoxColor = "P" (etc.) Then

There are a couple of things I'm not positive of as far as what exactly you want to do, but if I'm guessing right, maybe this will give you some alternative ideas.
It doesn't use the right click which can be done, but that's a whole other story and may be more involved than is desirable.
It will work on a double click in any cell in Column C (only).
Paste this in the worksheet module and see if it helps.
(Right click on the tab for the sheet of interest, choose View code and paste this in the white area on the right.
Then press AltQ to get back to the excel sheet & try it out.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Go:
If Target.Column <> 3 Then Exit Sub
BoxColor = InputBox("Enter S for Successful, P for Partial, F for Failed, or SH for Shadow. Click cancel if no change is needed.", "Support Success?")
If BoxColor = "" Then Cancel = True: Exit Sub
Select Case BoxColor
    Case "S"
        ActiveCell.Interior.ColorIndex = 4
    Case "P"
        ActiveCell.Interior.ColorIndex = 6
    Case "F"
        ActiveCell.Interior.ColorIndex = 3
    Case "SH"
        ActiveCell.Interior.ColorIndex = 15
    Case Else
        MsgBox "Please enter S, P, F, or SH", , "Invalid Entry"
        GoTo Go
    End Select
Cancel = True
End Sub

Hope it helps,
Dan

Edit:
I don't believe it! I must be getting better if I'm starting to come up with the same type of solution as HOTPEPPER!
(I just need to learn to type the text faster...)

 

[Bruiser616]

Thanks Guys!

Hotpepper,

I tried your code and couldn't get it to run. I was trying to figure out why when HalfAce's came in.

HalfAce,

Yours worked very well and does the job. Thank you very much! I like the Double-click and I'm sure it will be easier to use.

I believe I have some things to learn about the nuances of Excel.

Thanks again to both of you for the help!

Have a great day! :wink:

 

[HalfAce]

You're most welcome.
HOTPEPPER's code works great if you're in your original range (C1:D2).

And I stand corrected about the right click being more involved than is desirable. I had something altogether different in mind for that but HOTPEPPER made it look quite easy! :wink:

Dan