• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA to prevent multiple selections in column

W

westend9876

Member
I have two columns of Data Validation. Each for every cell consist of either blank or "X". The two column ranges are 'VA_IRRRL!R7:R33' and 'VA_IRRRL!S7:S33'.

1.) I need VBA code that prevents the user from selecting X more than once in each column. The user will have to delete the current X first before selecting another X in each respective column.

2.) Message box instructing user of the same

3.) Force another macro to run every time user makes another selection from each respective column. Here is the code for the current macro that should should be run on each new selection

>>> third time --- use code - tags as written in Forum Rules <<<​
Code: 
Sub GetRS_VA1and2_2()

GetRS_VA1_2
GetRS_VA2_2

End Sub
 
Last edited by a moderator:
Interesting. It sounds like you need "active content", that is, some VBA code that is triggered by an event (or more than one) in a worksheet. But by "select" do you mean clicking on a cell, or moving the cursor to it, or what? Because as I recall, there's no distinction between those two—and I don't think you want the worksheet to complain as the user merely moves the cursor if it happens to cross a second cell that has 'X' in it.

Is the user allowed to click on A6, then change his mind and click (or move the selection to) A14 (assuming both cells contain 'X')? If you don't object to that, but you want to keep him from clicking on A6 and then <Ctrl>-clicking on A14 (thus selecting both at once), I think you'd want to use the SelectionChange event. I've never done it, but it works at all I expect you'd write a module named Worksheet_SelectionChange, and then check each cell in the Selection to see whether it fails your requirements.

We can talk then about how to respond correctly. But first, what constitutes "selecting", for your purposes?
 
BobBridges said:
Interesting. It sounds like you need "active content", that is, some VBA code that is triggered by an event (or more than one) in a worksheet. But by "select" do you mean clicking on a cell, or moving the cursor to it, or what? Because as I recall, there's no distinction between those two—and I don't think you want the worksheet to complain as the user merely moves the cursor if it happens to cross a second cell that has 'X' in it.

Is the user allowed to click on A6, then change his mind and click (or move the selection to) A14 (assuming both cells contain 'X')? If you don't object to that, but you want to keep him from clicking on A6 and then <Ctrl>-clicking on A14 (thus selecting both at once), I think you'd want to use the SelectionChange event. I've never done it, but it works at all I expect you'd write a module named Worksheet_SelectionChange, and then check each cell in the Selection to see whether it fails your requirements.

We can talk then about how to respond correctly. But first, what constitutes "selecting", for your purposes?
Click to expand...
By selecting I mean actually clicking the cell and selecting an option from the drop down menu.
 
Oh, "drop-down". I've hardly ever used drop-downs; not sure I know anything about them. It probably is the Worksheet_Change event you need, then, rather than Worksheet_SelectionChange. Each time your Sub is triggered by the Change event, you'd need to look at the Selection: If it's in column n and the value is 'X', you'd examine all the other values in column n to be sure none of them are already 'X'. Does that sound like what you want, or have I misunderstood you?
 
BobBridges said:
Oh, "drop-down". I've hardly ever used drop-downs; not sure I know anything about them. It probably is the Worksheet_Change event you need, then, rather than Worksheet_SelectionChange. Each time your Sub is triggered by the Change event, you'd need to look at the Selection: If it's in column n and the value is 'X', you'd examine all the other values in column n to be sure none of them are already 'X'. Does that sound like what you want, or have I misunderstood you?
Click to expand...
Yes that sounds like what I need. I just don't know how to code it. Thank you.
 
Ok, when you're writing a VBA program that's triggered by an event you have to be pretty exact. I beat my head against the wall quite a while before I figured that out :). Look here for documentation on the Worksheet_Change event and how to write VBA code for it. I'll show you another sample below. This code has to go not in one of the modules you'd normally add, but in the module associated with the worksheet where the change is to be made—if you don't know what I mean, ask, because it won't work otherwise. Here's a sample program you can try out:
Code: 
Private Sub Worksheet_Change(ByVal org As Range)
  vrow = "row " & org.Row
  vcol = "column " & org.Column
  vloc = vrow & ", " & vcol & ","
  MsgBox "You just changed the cell in " & vloc & " to '" & org.Value & "'."
  End Sub

This program announces any change you make anywhere in the module's worksheet. Once you have that working you're on the way; at that point we can talk about getting it to do what you want it to do.
 
You must log in or register to reply here.
Back
Top