Restrict Worksheet Change Event To Multiple Non Contiguous Ranges
Nov 20, 2007
Can you have more than 1 worksheet change event on the same worksheet, if so, how do you name it to prevent the ambiguous name error. What code would I need to select a text value in colums e11:e15 based on the cell value in cell named STATE and place the selected value in cell e16. I have, thanks to this resource, one worksheet change event that selects a numeric value from any column E3,F3:F7 and places that value in cell C4. but the same code doesn't work for the new worksheet change event.
View 4 Replies
ADVERTISEMENT
May 10, 2006
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub
If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
GlngRow = Target.Row
Call Module3.ShowList1
End If
If Not Intersect(Target, Range("T:T")) Is Nothing Then
GlngRow = Target.Row
Call Module3.showlist4
End If
End Sub
What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........
View 6 Replies
View Related
Aug 4, 2006
I have this code working fine. It applies conditional formating to two rows ((K3:K65) and (J3:J65)) and checks when a value changes in row B to reaplpy the formatting. I'd like to had add more columns to it. It would have apply the same conditonal formatting on rows ((O3:O65) and (P3:P65)) but when a value changes in row (N3:N65)
So basically, I want to keep the first working part of the code and have it to check at more rows with a different reference for the conditionnal formatting.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim rngJ As Range
If Target.Count > 1 Then Exit Sub
Set rng = Union(Range("B3:B65"), Range("J3:J65"))
If Intersect(Target, rng) Is Nothing Then Exit Sub
Set rngJ = Range("K" & Target.Row)
Select Case rngJ.Value
Case ""
rngJ.Interior.ColorIndex = xlNone
rngJ.Offset(0, -1).Interior.ColorIndex = xlNone
Case Is >= 20.............................
View 2 Replies
View Related
Aug 29, 2006
I am trying to make a macro run automatically based on info entered in a cell. The problem is I need this code on at least three separate sheets in the work book. It doesent work when I try to use the same basic code. The code I am using is below.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lLastRow As Long
Dim rgArea As Range, rgCell As Range
Dim COL_B As Integer
COL_B = 2
Dim COL_X As Integer
COL_X = 24
Dim ROW_FIRST As Long
ROW_FIRST = 5
' Find the last row of date in the "Approved" worksheet
lLastRow = FindLastRow(SZ_WS_APPROVED, COL_B)
' Set a reference to the changed cells in column X
Set Target = Intersect(Target, Range(Cells(ROW_FIRST, COL_X), Cells(lLastRow, COL_X))).........................
View 5 Replies
View Related
Apr 2, 2008
I am trying to use excel to score a test. I want to tick a cell to do so. There is an excellente information about Tick Cell Upon Selection, posted here http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm. Its range is limited to (A1:A100)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
Target.Font.Name = "Marlett"
If Target = vbNullString Then
Target = "a"
Else
Target = vbNullString
End If
End If
End Sub
How can I increase the range so it would target C1:C30, E1:E30 ( total of 17 columns)?.
View 3 Replies
View Related
Jul 28, 2006
I have a question on how to define fixed rows on making cell blank on new selection change.
The code below affects all rows under coloumn 1.
Is there any way to affect only eg row 1 to 20?
I attached a sample file for reference.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strName As String
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
strName = Target.Name.Name
On Error Goto 0
If ActiveCell.Column = 1 Then
Application.EnableEvents = False
ActiveCell.Offset(, 1).Value = vbNullString
Application.EnableEvents = True
End If
End Sub
View 9 Replies
View Related
Jun 21, 2007
Long time since i've needed the expertise in this magnificent forum. I Have a problem with the ChangeEvent procedure. I have defined a range of two colums as can be seen in the posted code sample, but the Change Event procedure runs the procedures if ANY cells in the sheet is changed. How do I limit the Change Event to only the two colums that I have specified?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Range("L6:M1000") Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
On Error Goto 0
If Not Intersect(Target, Range("L6:M1000")) Is Nothing Then
If Target.Value = 1 Then
Target.Offset(0, 1) = "5) Gennemført"
End If
If Target.Value = "5) Gennemført" Then
Target.Activate
With ActiveCell
.EntireRow.Select
Selection.Font.Color = RGB(196, 196, 196)
End With
ActiveCell.Offset(0, 11).Select
End If
End If
UpdateColorDeadline
Target.Select
End Sub
View 3 Replies
View Related
Sep 9, 2007
I'm trying to create a sheet where clicking in a range brings up, in my users words, "a box I can type loads of comments in". They want some kind of flag in this cell showing if comments are posted or not. So far so ok, got the userform to pop up using the selection change event below and dump the actual comments somewhere the user won't look. A rather inelegant IF statement to see if there's anything in the dump cell gives them their flag.
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Intersect(Target, Range("B2:B300")) Is Nothing Then Exit Sub
UserForm1.TextBox1.Value = ActiveCell.Offset(0, 10).Value
UserForm1.show
End Sub
So what's the problem? When I select entire rows, the userform pops up. Is this unavoidable?
View 2 Replies
View Related
Nov 21, 2011
I have a workbook with 33 worksheets (31 date tabs and a Month To Date and Year To Date tab)
I want to have a clickable cell on each of the 1-31 date tabs to point to the MTD
I know I could do this easily with a hyperlink but I would rather have a clickable cell.
I have achieved this on an individual worksheet using the following code
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("$R$3")) Is Nothing Then Sheets("MTD").Select
End Sub
My question is: Do I have to copy this to every single worksheets code or is there some way have all sheets read the same private sub? (I guess it negates the point of a "private sub")
Way to do this as it would make editing any changes easier than having to do it 31 times.
View 3 Replies
View Related
Jan 20, 2014
I'm trying to run a macro anytime cells G2, J2, M2, O2, P2, S2, V2, Y2, AB2, AE2, AH2, AK2, AN2, AQ2 change.
Right now I have the below code which is working well, but I only have it set for G2. Do you know what the notation is to make the target range multiple cells?
Also, I use the xlDown command in my sorting code, but later on it reverts back to "A5:AT60"... is there any way I can remove these specific cell references? I want to avoid having to re-write the macro every time my selection shrinks or expands.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("G2")) Is Nothing Then
Rows("5:5").Select
[Code]....
View 3 Replies
View Related
Jul 20, 2007
if there is a way to select a group of cells, select another group of cells and keep the previously group of cells selected.
Example: ...
View 6 Replies
View Related
Mar 27, 2009
Is there a way to write a Worksheet_SelectionChange (ByVal Target As Range) event in module after creating a sheet in VBA? I constantly delete a sheet, then repopulate it with a new one that is empty, but I need to add some code that happens if they should change a particular cell. It worked when I ran it on a worksheet without refreshing, but as soon as I cleared and repopulated the sheet, it was gone. Is there a way to preserve this?
View 9 Replies
View Related
Jan 24, 2012
I have a worksheet that has a few ranges and I need a printarea statement that looks like this:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"
The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.
So, I tried something like this:
Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
View 9 Replies
View Related
Jul 29, 2008
I transposed them into a separate sheet and used that for my lists. I have to share the file with macs.
My pc is on Vista running Excel 2003. The macs are OSX Panther (not Leopard) and running Excel 2003.
Solution (?): if I make the combobox lists programmatically, it will work on the macs?
The UserForm1 with 3 comboboxes:
The comboboxes are not dependent on each other.
For each combobox, I need to have 2 columns.
For each column, I need to transpose 2 x ranges (they are NOT contiguous)
My attempts have been embarassing and futile.
What it should be: ....
View 9 Replies
View Related
Dec 23, 2009
I am trying to call a function that calculates forecast error (wMAPE). This function needs to be able to handle passing in non-contiguous ranges. I can't seem to figure out how to do that.
Function wMAPE(Forecasts As Range, Actuals As Range, Weights As Range) As Variant
Dim Denominator As Double
Dim Numerator As Double
Dim i As Long
Dim Fcst As Variant
Dim Act As Variant
Dim Wt As Variant
If Forecasts.Cells.Count Actuals.Cells.Count Then MsgBox ("Error: Arrays not same size")
If Forecasts.Cells.Count Weights.Cells.Count Then MsgBox ("Error: Arrays not same size")
Denominator = 0............
View 9 Replies
View Related
Jun 17, 2009
an event macro to change the font colour of a cell whose value changes as a result of a calculation.
View 9 Replies
View Related
Jan 27, 2008
I have a dynamic print range defined as follows:-
=OFFSET(Sheet1!$A$1,0,0,MATCH("PRINT_AREA_ROWS",Sheet1!$A:$A,0),MATCH("PRINT_AREA_COLUMNS",Sheet1!$1:$1,0)), lets say this equates to range("A1:D12")
I also have a workbook beforeprint event defined as follows:-
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.LeftFooter = "Pete"
End Sub
My problem is that after the Print Event has fired , the dynamic range has been converted into a static one i.e range("A1:D12"). I have tried this on all version of Excel from 97 to 2007 and it behaves the same so premably this must be by design rather than a bug?
Am I doing something wrong? What is the work around? I'd rather not have to create the dynamic range within the print event procedure unless I have to as my workbook has many, many tabs!
View 9 Replies
View Related
Jul 21, 2009
Attached is book in which, when a choice is selected from Drop Down list in ColumnF the macro has to do the need.
When the macro was written it was working well. But when I tried to change it as a Worksheet_SelectionChange event nothing is happenning even though a choice is selected from drop down list.
View 4 Replies
View Related
Dec 6, 2011
Basically the situation I have is Sheet2 has many references to cells in Sheet1. Sheet2 is for all intents and purposes a kind of nicely formatted report form, and Sheet1 is the input form.
My ultimate goal is to automatically resize row heights on Sheet2 when cell contents change on Sheet2.
Using a worksheet_change event isn't working I presume because it doesn't see the formula output change as a worksheet change, the worksheet_change is firing only when the input is changed in Sheet1.
how can I capture these formula output changes on Sheet2 (triggered from input on Sheet1) OR is there a way of making a particular sheets rows always adjust in height to best fit?
View 5 Replies
View Related
Jun 12, 2007
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies
View Related
Jan 10, 2009
looking to only allow a check ("x") in a column for a reconcile - type worksheet. Am I close?
Private Sub Worksheet_Change(ByVal Target As Range)
'Data protection. Only allow "x" in the "cleared" column. If anything else is entered, a message box informs the user
'and the cell contents are cleared.
Dim val As Variant
Dim msg As String
If ActiveCell.Value "x" Then
msg = "You can only enter an X in the cleared column."
ActiveCell.ClearContents
End If
End Sub
View 9 Replies
View Related
Feb 7, 2014
I am trying to run the below code whenever the result of cells (22,x) changes. Cells (22,x) contains a formula.
[Code] .....
View 2 Replies
View Related
May 28, 2009
trying to get a worksheet change event to work. Basically the code below calls the time_start procedure when cell J16 downwards is selected.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row > 15 And Target.Column = 10 Then
Call time_start
Else
End If
End Sub
However, I am lost from here on....Let me try and explain what it is I am trying to do. This seems so simple if you know how. Each time that the word "Completed" is entered in to a cell from J16 onwards, the cell two columns to the right on the same row is selected and the current date is entered.
If the word "Completed" is deleted, I want the date on that row to be deleted.
I would also like the choices "Completed" and a blank cell to be given in a validation list via a dropdown if possible to avoid occurances of "Complete" etc and mis-spellings but I understand that a bug may stop me from using this functionality. I am running Excel 2002 on a Windows XP Professional OS.
View 9 Replies
View Related
May 7, 2013
I have the below vba and would like to have the '''event calculated ''' on any event on the worksheet. Right now it only update if updating the val1 thru val6 cells. How can i get the worksheet to update on any event or change on the sheet?
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
fr = Range("a65536").End(xlUp).Row
Set Rng = Range("a2" & ":n" & fr)
If Intersect(Target, Rng) Is Nothing Then Exit Sub
[Code] .......
View 2 Replies
View Related
Mar 6, 2007
I have looked at the threads concerning cell change events but cannot find a solution to my situation. I have a worksheet with a cell using a validation list. I wish to exicute a procedure whenever the dropdown list is changed in that one cell. Everything I have seen in the Worksheet event threads is evaluating the contents of a cell and I am attempting to exicute if the cell changes.
View 2 Replies
View Related
Aug 22, 2007
I have a spreadsheet with a table of values in range E5 to T158.
A macro populates the table by looking up values on other sheets in the book. If the macro finds a value in the lookup for Row 7 of any column (ie E7,F7...T7) it populates the rest of the column with that value (E7 value gets pasted to E8:E158) THEN it protects the cells it pasted (E8:E158).
If the macro does NOT find a value for row 7, it simply skips it, leaving it blank, and continues to row 8 until it reaches row 158 of each column E to T.
I want to give the user flexibility with these values. So if the user either deletes E7 or changes the value of the contents in E7, I want to unprotect the cells of rows 8 to 158 for that column.
I have created a
Private Sub Worksheet_Change(ByVal Target As Range)
in the private module for that sheet below. I thought it was working but it isn't doing anything when I change or delete the value in Cell E7 for example. Please help!
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed
If Target.Cells.count > 1 Then Exit Sub 'Or IsEmpty(Target)
View 5 Replies
View Related
Nov 8, 2012
I have been looking at a post number 170404 which NBVC answered with an example grid. This was while I was trying to bring searched data through to another sheet and this is just what I was looking for as a brilliant start,
I have attached my very similar example in my scenario. I have also got NBVC's example on the first two sheets of this as reference. In addition I am also bringing through from-to date ranges which I then need to be applied to a Booking Sheet so I can see that these dates are booked for this person, and ideally where they are going and what they need to do there by colour.
I tried, just as a test in this example the following. Please note that this only had the three ranges as I was testing an example coordinator which returned three date ranges. It could be that the example returns 20 or 30 ranges that need to be applied to the rather primative Booking Sheet.
=IF(OR(AND(C10>='Coordinators list'!F5,'Coordinators list'!L5<=C10),(AND(C10>='Coordinators list'!F6,'Coordinators list'!L6<=C10)),(AND(C10>='Coordinators list'!F7,'Coordinators list'!L7<=C10))),"yes","no")
It seemed to give the right answer initially but trying different dates it gave the wrong result. It seemed to be checking one of the ranges and not all of them. As mentioned above this was just an example to see what I could get working but I don't think that this would be the ideal way of doing this as it would need to check 30-40 maybe more ranges depending on what comes back from the coordinator search.
The aim would be to choose a co-ordinator on the Site Planner sheet and ideally return the city rather than the yes/no in the Booking Sheet for that date and use conditional formatting so that if the type from the Site Planner Sheet was paint it would fill in one colour, clean in another, etc.
Was hopefully trying to get this done without using any VB as I am trying to resolve this for my Wife she isn't very tech savvy and and I need to be able to explain the formulas!
I'm sure you will see from my sheet I have given this a good go but I am a bit unsure on what some of the formulas on NBVC sheet are doing, and that this is my first attempt at doing anything like this. I have also tried using lookup and match but I'm still having issues that they either aren't always in date order and it seems to return no if it doesn't match the first range. The other main issue that I have seen is that I need to check whatever ranges the selection comes back with rather than adding each one to search through.
I hope this becomes a bit clearer on the sheet I have attached,
View 9 Replies
View Related
Nov 22, 2011
I'm trying to create a worksheet change event macro that will change the colour of the cell when you update the value in that cell.
I have column C with 140 rows and when one of the cells in the column is changed by the user, I need the same cell to highlight in yellow. The highlight would then be removed once the user has acted on the change in the value, and that'll be done in another macro.
I understand I need a worksheet change event macro in the specific sheet and need the following code for yellow:
Code:
.Interior.Color = RGB(255,255,0)
, but any further I get stuck as it involves 'target addresses'
View 2 Replies
View Related
Oct 1, 2013
combine two Worksheet Change event macros into the one macro? The macros are listed below.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)On Error GoTo exitHandler
Dim rngDV As Range
Dim lRow As Long
Dim lCol As Long
[Code]...
exitHandler:
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("O:O")) Is Nothing Then
Application.EnableEvents = False
[Code]...
View 7 Replies
View Related
Nov 30, 2006
I have the following code that should pop up a message when column E is left blank when "other" is typed into column D. But I cannot get it to work.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range, LastRow As Range
Dim x As Integer
If Target.Count > 1 Then Exit Sub
Set rng = Range("F:F")
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target = "" Then
For x = 1 To 5000
If Cells(x, 4).Value = "Other" And Cells(x, 5).Value = "" Then
Answer = MsgBox("If other, please state", vbOKCancel, "CONFIRMATION")
End If
Next x
End If
End Sub
View 9 Replies
View Related
