Worksheet Change: Data In A Range, And Hits The "delete" Key Once

Oct 3, 2006

I have code in Worksheet_Change, and it works great when a user is filling in the data. When the user deletes data one row at a time, it also works. However, what winds up happening, is the user selects all their data in a range, and hits the "delete" key once, and then I get an error. How can I check whether or not the target is multiple rows and columns?

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column = 1 And Target.Row > 15 Then
If Target.Value < 1000 And Target.Value <> "" Then
'My logic goes here
End If
End If
Application.EnableEvents = True
End Sub

View 2 Replies


ADVERTISEMENT

When Close Worksheet Its Reopens The User Form And Hits Start

Oct 23, 2009

I have a userform, every time the user clicks on the start button it recodes there name, today’s date and their start time when they hit finish it records their end time.

The next time the user hits start it will record the users name, today’s date and start time on the next row (below their last entry)

The problem I have is that if the user clicks on start and then does not click on end button, but instead exits excel, then reopens the user form and hits start. The start will go on the row below, but now when they click on the end button the end time will go into the previous row for the end time, as they did not put it in if you know what I mean.

User NameDateStartEndTotal TimeTime InactiveClaire Watts
23/10/0915:04:2515:04:420:00:170:00:17Claire Watts
23/10/0915:04:4715:04:540:00:070:00:05Claire Watts
23/10/0915:04:5515:05:060:00:110:00:01Claire Watts
23/10/0915:05:0815:06:100:00:150:00:02Claire Watts
23/10/0915:05:55

#######Claire Watts
23/10/0915:06:18

15:06:18


I want to stop the user from being able to exit excel if they had clicked on start

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "You have clicked on Start and therefore mus Click the End button before you can exit excel.", vbCritical, "Cannot Close"

End Sub

This disables the X in the top right hand corner, I have aready disabled the one in the user form. However the following code does not let the user exit via File and Exit. Is there a way to put an IF statement into this that say if CmdStart has been clicked then the user can not exit, How every if it has not then they can exit.

I have read threads on this forum as well as on others and this does seam to be and issue.

OR is there a way of Hiding the X button that is in the top right hand corner. However this will still allows the user to hit start button and then exit excel, which will continue with the problem.

View 10 Replies View Related

Create Graph Of Date Hits Using Weekly Range?

Aug 5, 2013

I'm trying to create a line graph to show the trends of usage. I have a list of dates that I pulled from a website. I am trying to create a function that will count the number of dates between, for example, 8/5/2013 and 8/11/2013.

I had tried using this function to create a set of weekly ranges. =TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7, "m/d/yyyy h:mm AM/PM")&"-"&TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7+6,"m/d/yyyy h:mm AM/PM")

It correctly displays the date ranges, however when using this function:
=COUNTIF($E$2:$E$305, A2) Nothing gets pulled.

Here is one of the dates that has been pulled for example: 8/5/2013 11:10:00 AM

View 5 Replies View Related

VLookup Return Multiple Hits From Same Column Of Data

Jul 14, 2009

I am running a Vlookup on a large set of ID#'s that exist accross multiple servers.

The data is structured in (2) columns how you see below , and you will notice that the same ID# can show up on multiple servers.

However, the VLOOKUP is only returning the first match it finds.

Assuming that I want to keep the data structured as is, how do I make the vlookup return all matches?

ID# LOCATION

23 SERVER1
34 SERVER4
23 SERVER2
13 SERVER1
17 SERVER3
34 SERVER1

View 10 Replies View Related

Worksheet Events: Use A Worksheet Event To Change The Background Of The Current Month Two Columns In The Range To Yellow Color

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

Macro To Change Range / Action Depending On Worksheet (range To Autofill)

Sep 17, 2013

Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data

On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14

I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:

Here is my macro:

Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet

[Code]....

View 8 Replies View Related

Change Timestamp When Worksheet Range Changed?

Feb 11, 2014

I am trying to have one cell (G5) populate with a timestamp when a change is made to any cells in a range (some are merged). The code below worked for a second, until it didn't.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F8:G33")) Is Nothing Then Exit Sub
Range("G5").Value = Now()
End Sub
*Getting a yellow arrow by the row starting with Range("G5")

View 6 Replies View Related

Restrict Worksheet Change Event To Specified Range

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

Change Worksheet Named Range To Absolute Values?

Feb 28, 2012

Have a query that pulls general ledger data. Named Range SALES defined for Sales Account numbers. What is the best way to always have SALES (which pulls as negative number) converted to absolute values?

I have in my mind that it would be some sort of worksheet change event . . . but I don't really grasp the byval/byref TARGET concept by itself, let alone whether it can work on a named range.

View 5 Replies View Related

Excel 2007 :: Monitoring Change In Certain Range At Worksheet?

Jul 9, 2012

I wish to monitor a change in a certain columns at some worksheets. I have - after having consulted John Walkenbach' fantastic book Excel 2007 Power programming with VBA - understood how I do to monitor a change in specific worksheet, by using the Intersect-function in the change-event on that particular worksheet. That works fine.

My situation is that I have an application that I start out with a certain number of workheets in a workbook, and in the course of the work that the application does, it now and then adds worksheets, and these worksheets I would like to monitor for change in a special column. I know, by looking at the names of the worksheets which ones of those in the collection that I want to check.

I therefore used, not the Worksheet_Change event but the Workbook_SheetChange. It doesn't work quite well. I guess my question for now would be:

The call to this event looks like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

1) How - in what way - should I use the first argument, the "Sh as Object"? Can I/Should I give it a name?

The error message I get when I use an instruction like this:

If Not Intersect(Target, Range(M:M)) Is Nothing Then
'Do Stuff
end if

The method 'Intersect' in the object '_Global' failed

(M is the column that I want to check)

View 4 Replies View Related

Restrict Worksheet Change Event: Non-Contiguous Range

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

Change A Worksheet Tab Colour Dependant On The Contents On A Range Of Cells?

Mar 24, 2014

I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.

So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"

View 5 Replies View Related

Make Worksheet Calculate Event When Range Of Cells Change?

Feb 15, 2014

I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen?

[Code] .....

View 4 Replies View Related

Sub Worksheet Change Range Color Error Due To Protected Sheet

Jul 18, 2006

I have this code that works fine until I password the sheet and the range (b9:d65536) is protected. When I protect the sheet and make a change I get a debug error. What would I need to add/change to handle protected cells on a protected sheet?

VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....

View 9 Replies View Related

Update Data By Ado - Change/delete Data And Then Run Macro For Update Data In Source Spreadsheet

Dec 6, 2006

I have one source spreadsheet, where are columns NAME, DATE. I read these data by ADO to other spreadsheet, where I can change/delete data and then run macro for update data in source spreadsheet. The problem: In source spreadsheet is column "NAME" and column "DATE", with values e.g. "Joseph"; 1.1.1980. I read this data to other spreadsheet, then I delete in it value 1.1.1980. When I run macro Update, it messages error.

Sub UpdateItem
...
.Fields.Item(1).value = activecell 'activecell value = "Joseph"
If Not isempty(activecell.offset(0,1)) Then
.Fields.Item(2).value = activecell.offset(0,1).value
Else
.Fields.Item(2).value = "" 'I tried Empty and 0 too but when I read data again then, it displays 0.1.1900, nothing works
End If
...
End Sub

It seems that in source spreadsheet has data in column "Date" format Date and when I try to update data in format String ("") in source spreadsheet by Update macro, it messages error. When I used

.Fields.Item(2).Value = Empty
' or
.Fields.Item(2).Value = 0

after rereading data it displays 0.1.1900 What I want to get is that if the cell with date (in other spreadsheet) is empty, the cell in column Date in source spreadsheet after updating will be blank (contains no values).

View 2 Replies View Related

Graphing Time To A Sum Of Hits

Dec 1, 2006

I have a graph / time problem

I have a program that is spitting out raw data to me that I would like to be able to put into a graph form in a efficient manner, but I’m not sure the best way to accomplish this.

Here is what I’m working with:
I am getting a list of time date stamps that appear as follows.

11/30/2006 6:02:25 AM
11/30/2006 6:02:36 AM
11/30/2006 6:02:40 AM and so forth.

Each stamp represents a hit to a link.
What I would like to be able to do is graph the number of hits per hour verse the time of day hit. Showing the time on the x & the number of hits per that hour on the y axis.

I can convert the time to decimal, but I still need to find a way to get excel to graph the sum of hits per hour.

View 9 Replies View Related

Hits An External Site

Dec 18, 2006

I am working on a spreadsheet that hits an external site. My questions is, I need to hit the site about 80 times becuase I search for different info. It there a way to get the vba to go down a list 80 of names rather than me entering the vba code 80 times with the different names?

View 9 Replies View Related

Excel 2010 :: Macro To Delete Last Line Of Data In Each Worksheet In Workbook?

Apr 24, 2014

I'm trying to create a macro that will look at each worksheet in a workbook and then delete the last line of data on each worksheet. The last row can vary on each worksheet. This is what I have come up with but it is not working. I am on Excel 2010 and Windows 7.

Sub Macro1()
Macro1 Macro
Dim ws As Worksheet

[Code]....

View 2 Replies View Related

2003 Data Validation Doesn't Trigger Worksheet Change

Dec 31, 2008

I have a bit of code that calls a formatting sub depending on which cell is modified. It is triggered by the Worksheet_Change event, determines which cell is modified, and either calls the formatting sub or doesn't based on the location of the modified cell.

Some of the columns in the sheet have data validation with drop downs. If I select a value from the drop down, it doesn't trigger the Worksheet_Change. If I type a value into the same cell, it does.

This was apparently an issue in Excel '97, but supposedly fixed in '03?

View 9 Replies View Related

Delete Range Of Data

May 20, 2009

Picture a simple spreadsheet (A1:C10). In column A, the word "delete" appears on every even numbered row (2, 4, 6, etc). Blank (no) data appears on the odd numbered rows (in Column A).

The following code was to sort A1:C10 by column A, bringing all the blanks to the top (now, rows 1 through 5) and "deletes" to the bottom (now rows 6 through 10). I then want to delete all the rows which contain the word "delete."

Selection.Find(What:="DELETE", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("A185").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.EntireRow.Delete
Range("B184").Select
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

My problem is that each time the macro is run, the word delete will appear on a different row. Can anyone suggest how to fix the above code so that it simply finds the word "delete" (after the sort), then does the equivalent of [END][DOWN], [END RIGHT] (to highlight the desired ranged to be deleted, then executes the delete command.

View 9 Replies View Related

How To Delete Data Particular Range Manually

Mar 26, 2014

I have excel sheet it contains 200000 records.I want to delete records from 50000 to 200000.Is there any shortcut to delete data.

View 2 Replies View Related

Alter Worksheet Change Event At Specific Time After Data Manually Changed

Jul 16, 2014

I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.

After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..

But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...

I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.

So what can be done to achieve..

View 7 Replies View Related

Excel 2010 :: Search And Highlight Hits Only In Column C?

Jun 11, 2013

In an Excel 2010 worksheet I use three columns. I need a user to be able to input a word or phrase into a cell or input box, and have Excel highlight that word or phrase every place it occurs only in column C.

Can I do that with conditional formatting? Or do I need to use a function? Or do I need a macro?

View 5 Replies View Related

Macro To Go Through One Column Until It Hits Blank Cell And Stop?

Mar 25, 2014

I need a macro to go through one column until it hits a blank cell and stop... the criterea of the loop:

if cell = "Multi" then **Move to X tab** else **move to X2 tab**

View 1 Replies View Related

Worksheet Change Event :: (ByVal Target As Range) Event In Module After Creating A Sheet

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

Worksheet 'Change' Event: Show Which Rows Of Data Have Been Changed By Incrementing A Revision Attribute

May 13, 2009

I want to show which rows of data have been changed by incrementing a revision attribute. For example, if a user changes the contents of a cell anywhere between rows 2 and 13 and col 1 and 9 then the revision attribute in col 10 would increment from 1 to 2 (for the affected row). If another change affects the same row then the rev attribute would increment to 3, and so forth. I don't care which cell was changed only that something on that row was touched.

I thought the CHANGE event was a dead ringer for triggering some VBA code to control this but, since part of the change event code writes the revision value, this triggers another CHANGE event causing an endless loop until something (??) kicks in and stops it after 220 iterations. Is there a way to inhibit the change event just prior to updating the cell containing the version attribute?

Rather than post the code here I have submitted the workbook that includes the whole setup and code. I should also mention I looked at all the other worksheet events and I do not see any "triggers" that would fire each time a cell content is changed. As a side note, is there a way to step into the code of a change event? F8 does nothing.

View 3 Replies View Related

How Can I Stop The Chart Data Range From Updating When I Add & Delete A Row

Oct 11, 2009

I have a worksheet with 300 rows and I have a separate worksheet that contains graphs based on the data in the 300 rows.

The data is continually updated but stays at 300 rows. I add a new row at 301 and then I delete row 1.

I set the graphs up with a Chart Data Range of =GData!$B$1:$AB$300

As I add and delete rows the Chart Data Range reduces so after adding and deleting 3 rows the Chart Data Range is now =GData!$B$1:$AB$297

How can I get the Chart Data Range to stay at =GData!$B$1:$AB$300?

View 9 Replies View Related

Pivot Table Change Data Range?

Jan 30, 2012

I have created Pivot Table in "Sheet2" and the source data is in "Report" tab. The source data is in the range of A1:K200 -means until the last cell of the excel. Now if the data is more then this we need to change the source data every time before we refresh the pivot table - Is there any macro or any way so the range can be change automatically ?

View 9 Replies View Related

VBA To Set Range Based On Change In Column Data

Jun 1, 2012

I have a spreadsheet in which column "c" represents a department: 01, 02, 03, etc. every time this value changes I want to do a subtotal. The code I've come up with thus far is:

Sub Subtotal()

Dim lrow As Long
For lrow = Cells(Cells.Rows.Count, "c").End(xlUp).Row To 2 Step -1
if cells(lrow, "c") cells(lrow-1, "c" then range
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4, 5, 6), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

But, I can't figure out how to get set my range to include all the data that pertains to a department to do the subtotal.

View 3 Replies View Related

Copy Data With Criteria For Worksheet And Data Range

Oct 17, 2012

I have a workbook with 3 sheets

Sheet1 - TeamList - Column A is a list of all the team leaders which is used to create a new worksheet for every team. Col B is blank. Column C, D, E are the team lists where teams can consist of anywhere from 10-25 employees.

For example
Column A
Column B
Column C
Column D
Column E

Team Leaders

Team Leader
Employee
Pin

TL 1

TL 1
Emp 1
10001

[Code] ......

Sheet2 "RawHours" A raw data sheet that contains employee hours information

For Example
Employee #
PIN
NAME
Reg
OT
TRN
Stat
Sick
Vac
Berv

2000100
10001
Emp 1
24.00
0.00

[Code] .......

Sheet3 - Template

Basically what I want to be able to do is create a sheet for every team leader and copy and paste there team into the appropriate sheet.

I have the sheet create done by simply copying the template and renaming each sheet using the Team Leaders list from column A from the TeamList sheet. And then I copy the page setup information using the template as well but when it comes to pulling over the appropriate team members in to the appropriate sheet I can not seem to wrap my head around it.

Below is the current code I am using:

Code:
Option Explicit
Sub CreateSheet()
Dim LR, i As Long
Dim shCount As Integer
Dim Sh As Worksheet
Dim Cell As Range
Dim CpySht As PageSetup

[Code] ........

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved