Prevent clicking on a cell
in [Programming]
|
From: ordnance1 on 27 Mar 2010 12:30 I want to run the code below to prevent a range of cells from being selected if the Range("Q7") = 1. I have all cells on the worksheet locked but the user must be able to click on the locked cells to trigger a userform so I have to check Select Locked Cells. So is there any way make the Range("B5:C5") unselectable? If Range("Q7") = 1 Then Range("B5:C5").Locked = True End If
From: Mike H on 27 Mar 2010 13:13 Hi, >So is there any way make the > Range("B5:C5") unselectable? No but you can stop them staying there. Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5:C5")) Is Nothing Then MsgBox "Hey you out of there!!!" Target.Offset(1).Select End If End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "ordnance1" wrote: > I want to run the code below to prevent a range of cells from being selected > if the Range("Q7") = 1. I have all cells on the worksheet locked but the > user must be able to click on the locked cells to trigger a userform so I > have to check Select Locked Cells. So is there any way make the > Range("B5:C5") unselectable? > > > > If Range("Q7") = 1 Then > Range("B5:C5").Locked = True > End If > > . >
From: Rick Rothstein on 27 Mar 2010 13:31 You could try this concept where I control the ability to select those cells via worksheet event procedures (so if you are already using these event procedures, then you will have to merge my code into them). Put the following code in the code window for the worksheet that you want to have this functionality. Note that if Q7 is not currently 1 and either B5 or C5 is the ActiveCell, either whenever you activate the sheet or if you change Q7 to 1 via code, then my code will make A2 the active cell. If you want a different cell to become active under either of these two conditions, then change my Range("A2") to reflect the cell you want to use in both the Activate and the Change event procedures. '*************** START OF CODE *************** Dim LastCell As String Private Sub Worksheet_Activate() If Range("Q7").Value = 1 And Not Intersect(ActiveCell, _ Range("B5:C5")) Is Nothing Then Range("A2").Select LastCell = ActiveCell.Address End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address(0, 0) = "Q7" And Target.Value = 1 Then If Not Intersect(ActiveCell, Range("B5:C5")) Is Nothing Then Range("A2").Select End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("B5:C5")) Is Nothing Then If Range("Q7").Value = 1 Then Range(LastCell).Select End If LastCell = ActiveCell.Address End Sub '*************** END OF CODE *************** -- Rick (MVP - Excel) "ordnance1" <ordnance1(a)comcast.net> wrote in message news:eZB3krczKHA.5348(a)TK2MSFTNGP02.phx.gbl... > I want to run the code below to prevent a range of cells from being > selected if the Range("Q7") = 1. I have all cells on the worksheet locked > but the user must be able to click on the locked cells to trigger a > userform so I have to check Select Locked Cells. So is there any way make > the Range("B5:C5") unselectable? > > > > If Range("Q7") = 1 Then > Range("B5:C5").Locked = True > End If
From: ordnance1 on 27 Mar 2010 13:51 Thanks that worked great! Below is my final code. My only problem now is that since there will be over 80 of these statements I would like to move this out of the SheetSelectionChange so I added the following line: Module3.BlankDays but I get an Object Required error. What have I missed? Sub BlankDays() If Range("Q7") = 0 Then If Not Intersect(Target, Range("B5:C5")) Is Nothing Then MsgBox "You have selected a day that is not available for vacation. Please reselect." Range("A3").Select End End If End If End Sub "Mike H" <MikeH(a)discussions.microsoft.com> wrote in message news:1F6CAEB4-AF38-47B3-95C9-80CB61E10DEC(a)microsoft.com... > Hi, > >>So is there any way make the >> Range("B5:C5") unselectable? > > No but you can stop them staying there. > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range) > If Not Intersect(Target, Range("B5:C5")) Is Nothing Then > MsgBox "Hey you out of there!!!" > Target.Offset(1).Select > End If > End Sub > -- > Mike > > When competing hypotheses are otherwise equal, adopt the hypothesis that > introduces the fewest assumptions while still sufficiently answering the > question. > > > "ordnance1" wrote: > >> I want to run the code below to prevent a range of cells from being >> selected >> if the Range("Q7") = 1. I have all cells on the worksheet locked but the >> user must be able to click on the locked cells to trigger a userform so I >> have to check Select Locked Cells. So is there any way make the >> Range("B5:C5") unselectable? >> >> >> >> If Range("Q7") = 1 Then >> Range("B5:C5").Locked = True >> End If >> >> . >>
From: Rick Rothstein on 27 Mar 2010 15:05 Just noting... your (new) posted code shows you checking for Q7=0 whereas your original posting showed you wanted to block access if Q7 = 1 (implying access to those cells was okay to do when Q7 was a different value from either the 0 or 1, whichever you actually meant). I would point out that, when running as event code, and assuming access was alright under certain circumstances, Mike's suggestion leaves some situation not completely covered. I'll await your clarification for the above (and what follows) before delineating the problems as I see them. Now, the problem you are experiencing is due to the fact that you moved suggested event code (which must be placed in the worksheet's module) to a general module. Two points about having done that... one, the code will not execute automatically from the general module (event code must be in a worksheet module and be in event produces, not macro Subs, in order to function automatically); and two, the Target object your code references only exists within (certain) event procedures in a worksheet module... VB has no idea what Target is if you call it from a general module. I'm not sure I understand why you think you have to move the code to a general module... the worksheet module is more than capable of handling lots of code (the same amount as a general module by the way). With that said, you might not actually need 80 individual sections of code depending on how similar the 80 individual sections of code will be. Can you give us some more detail the 80 individual sections you are envisioning (like the ranges they apply to, assuming the rest of the code is the same)? -- Rick (MVP - Excel) "ordnance1" <ordnance1(a)comcast.net> wrote in message news:uUaXmYdzKHA.2644(a)TK2MSFTNGP04.phx.gbl... > Thanks that worked great! > > Below is my final code. My only problem now is that since there will be > over 80 of these statements I would like to move this out of the > SheetSelectionChange so I added the following line: > > Module3.BlankDays > > but I get an Object Required error. What have I missed? > > > > Sub BlankDays() > > If Range("Q7") = 0 Then > If Not Intersect(Target, Range("B5:C5")) Is Nothing Then > MsgBox "You have selected a day that is not available for vacation. > Please reselect." > Range("A3").Select > End > End If > End If > > End Sub > > > > > "Mike H" <MikeH(a)discussions.microsoft.com> wrote in message > news:1F6CAEB4-AF38-47B3-95C9-80CB61E10DEC(a)microsoft.com... >> Hi, >> >>>So is there any way make the >>> Range("B5:C5") unselectable? >> >> No but you can stop them staying there. >> >> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range) >> If Not Intersect(Target, Range("B5:C5")) Is Nothing Then >> MsgBox "Hey you out of there!!!" >> Target.Offset(1).Select >> End If >> End Sub >> -- >> Mike >> >> When competing hypotheses are otherwise equal, adopt the hypothesis that >> introduces the fewest assumptions while still sufficiently answering the >> question. >> >> >> "ordnance1" wrote: >> >>> I want to run the code below to prevent a range of cells from being >>> selected >>> if the Range("Q7") = 1. I have all cells on the worksheet locked but >>> the >>> user must be able to click on the locked cells to trigger a userform so >>> I >>> have to check Select Locked Cells. So is there any way make the >>> Range("B5:C5") unselectable? >>> >>> >>> >>> If Range("Q7") = 1 Then >>> Range("B5:C5").Locked = True >>> End If >>> >>> . >>>
|
Next
|
Last
Pages: 1 2 Prev: Copy Picture problems Next: FASTER USE OF THE REPLACE() FUNCTION !!!!! |