MsgBox if Selection is Not in a Specific Row

canam

Board Regular
Joined
Dec 14, 2004
Messages
156
I have a macro that updates a sheet based on the active cell that is currently selected. This active cell needs to be in column B. If a cell from any other column is selected it will cause problems. What I would like to do is have code run in the macro before anything is done that shows a message box if the active cell is in any row other than B. Can anyone help with the code to accomplish this? Thanks
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Code:
If ActiveCell.Column <> 2 Then
MsgBox "Select Cell in B" & vblf & "Routine Will Now Terminate",vbcritical,"Error"
Exit Sub
End If
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

Code:
If Intersect(ActiveCell,Range("B:B")) Is Nothing Then
  'Activecell isn't in B column - take appropriate action
Else
  'Activecell is in B column - success!
End If
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
That's fairly straight forward
Code:
If ActiveCell.Column <> 2 Then
    MsgBox "You're not in column B"
    Exit Sub
End If

However, if your macro depends on the active cell being in a specific column, that indicates your code uses alot of select and activate. You might be well served spending a little time eliminating select from your code. You don't have to select cells/sheets in order to manipulate them...And add sheet names to each Range reference

Example.

Sheets("Sheet2").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
Range("B1").Select
Selection.PasteSpecial xlpastevalues

that could be changed to

Sheets("Sheet2").Range("A1").Copy
Sheets("Sheet1").Range("B1").PasteSpecial xlpastevalues

Basically anywhere you see Selection, replace that with whatever was previously selected.

You're code will be much faster, and not dependant on where the user puts the active cell.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,850
Members
414,342
Latest member
K Darrell Smith

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