Fairly easy code, so what's the problem?

Bruiser616

New Member
Joined
Feb 19, 2005
Messages
8
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!
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, welcome to the board!

Try this in the Worksheet BeforeRightClick event:

Code:
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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.
Code:
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! :cool:
(I just need to learn to type the text faster...)
 

Bruiser616

New Member
Joined
Feb 19, 2005
Messages
8
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

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top