Target Range Activation

Trying to get Wooksheet_Change to work:

Public rng As Range
Private Sub Workbook_Open()
    Dim range1 As Range
    Set range1 = Sheet4.Range("D4:F500")
    Set rng = range1

'I need a way to access the subroutine Worksheet_Change 
'to fire the object Target range. I tried: 
'Worksheet_Change(rng)        that didn't work.

End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    
End Sub
0
Utf
1/24/2010 9:13:01 PM
excel.programming 6508 articles. 1 followers. Follow

11 Replies
761 Views

Similar Articles

[PageSpeed] 44

Detail on what you are trying to do.??

-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:29A5613F-6BA9-4256-AD55-099235798EC1@microsoft.com...
> Trying to get Wooksheet_Change to work:
>
> Public rng As Range
> Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
> 'I need a way to access the subroutine Worksheet_Change
> 'to fire the object Target range. I tried:
> 'Worksheet_Change(rng)        that didn't work.
>
> End Sub
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> End Sub 

0
Don
1/24/2010 9:22:56 PM
This code works

Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
    MsgBox "Cell in D4:F500 has be changed"
  End If
End Sub

Remember it must be added to the Sheet model not the Genaeal module
One way is to right click the sheet's tab and select View Code

best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Philosophaie" <Philosophaie@discussions.microsoft.com> wrote in message 
news:29A5613F-6BA9-4256-AD55-099235798EC1@microsoft.com...
> Trying to get Wooksheet_Change to work:
>
> Public rng As Range
> Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
> 'I need a way to access the subroutine Worksheet_Change
> 'to fire the object Target range. I tried:
> 'Worksheet_Change(rng)        that didn't work.
>
> End Sub
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> End Sub 

0
Bernard
1/24/2010 9:33:37 PM
The Worksheet_Change procedure MUST be located in the code module for
the worksheet whose changes you want to trap.  Each worksheet whose
changes need to be trapped will have its own Change event procedure in
its own code module. If you have Worksheet_Change in the ThisWorkbook
module, VBA does not recognize it as an event procedure and the
procedure will not be called automatically. VBA sees is as just
another ordinary procedure.

If you want to trap changes for all sheets in the ThisWorkbook module,
use the workbook's SheetChange event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
' your code here
End Sub

In this procedure, Sh references the worksheet on which the change
occurred, and Target references the cells on Sh that were changed.
This will trap changes on any sheet in the workbook (but not in other
workbooks).

For much more information about event procedures, see
http://www.cpearson.com/excel/Events.aspx .

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]




On Sun, 24 Jan 2010 13:13:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>Trying to get Wooksheet_Change to work:
>
>Public rng As Range
>Private Sub Workbook_Open()
>    Dim range1 As Range
>    Set range1 = Sheet4.Range("D4:F500")
>    Set rng = range1
>
>'I need a way to access the subroutine Worksheet_Change 
>'to fire the object Target range. I tried: 
>'Worksheet_Change(rng)        that didn't work.
>
>End Sub
>Private Sub Worksheet_Change(ByVal Target As Range)
>    
>End Sub
0
Chip
1/24/2010 10:23:19 PM
I am trying to set the Target from the Workbook_Open subroutine so the 
Worksheet_Change will fire when the cell in that range has been changed.  I 
need a way to get the object in Workbook_Open subroutine to set the object in 
Worksheet_Change to initiallize Target range. 
0
Utf
1/24/2010 10:36:01 PM

"Bernard Liengme" wrote:

> This code works
> 
> Sub Worksheet_Change(ByVal Target As Range)
>   If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
>     MsgBox "Cell in D4:F500 has be changed"
>   End If
> End Sub
> 
> Remember it must be added to the Sheet model not the Genaeal module
> One way is to right click the sheet's tab and select View Code
> 
> best wishes
> -- 
> Bernard Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme

 I already have this in my code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trgt As Range
    Dim intersect As Range
    Set intersect = Application.intersect(trgt, Target)
    If Not intersect Is Nothing Then
        With Sheets("Sheet4")
        For n = 2 To 500
            Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
        Next n
        End With
     End If
End Sub
0
Utf
1/24/2010 10:43:01 PM
I would like to set the Target from the Workbook_Open subroutine so the 
Worksheet_Change will fire when the cell in that range has been changed. I 
need a way to get the object in Workbook_Open subroutine to set the object in 
Worksheet_Change to initiallize Target range. 
0
Utf
1/24/2010 10:54:01 PM

This has been answered several times. Do NOT (!) put your
Worksheet_Change code in the ThisWorkbook code module. It MUST reside
in the code module of the worksheet whose changes you want to trap.
You don't call Worksheet_Change directly; Excel calls it automatically
when a cell value is changed. It calls the Change event code that is
in its own code module. If you put Worksheet_Change in the
ThisWorkbook module, Excel doesn't recognize it as an event procedure
and will not execute it. 

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 24 Jan 2010 14:54:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>I would like to set the Target from the Workbook_Open subroutine so the 
>Worksheet_Change will fire when the cell in that range has been changed. I 
>need a way to get the object in Workbook_Open subroutine to set the object in 
>Worksheet_Change to initiallize Target range. 
0
Chip
1/24/2010 11:39:44 PM
You have all my code.  Why can't I figure out why the Worksheet_Change is not 
firing when a cell in the range is manipulated?  I do save and exit the 
workbook before I run the updated program and I am using a xls file in Excel 
2007.
0
Utf
1/25/2010 1:59:01 AM
In this code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim trgt As Range
    Dim intersect As Range
    Set intersect = Application.intersect(trgt, Target)
    If Not intersect Is Nothing Then
        With Sheets("Sheet4")
        For n = 2 To 500
            Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
        Next n
        End With
     End If
End Sub

trgt is never set to anything.

Are you trying to pass that range from the workbook_open event to that sheet's
_change event?

If yes, you could create a new module (Insert|Module) and put this into the
module:

Option Explicit
Public trgt as range

Then in the ThisWorkbook module, change your workbook_open procedure to use that
public variable:

Private Sub Workbook_Open()
    Set trgt = Sheet4.Range("D4:F500")

Since trgt is declared in a General Module, every procedure in every module can
see it.

Then make sure you delete the declaration in Sheet4's worksheet module.  So the
code becomes:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim myIntersect As Range 'don't use a variable named Intersect!
    Dim N as long
    Set myintersect = Application.intersect(trgt, Target)
    If Not myintersect Is Nothing Then
        With Me 'the sheet owning the code.
          'stop this event from firing itself
          application.enableevents = false
          For n = 2 To 500
           'added a leading dot to the first cells() reference      
            .Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
          Next n
          application.enableevents = true
        End With
     End If
End Sub

==========
If you wanted this event to fire each time you opened the workbook, then you
could just change a value to itself in the range you want to inspect.

In the ThisWorkbook module:

Private Sub Workbook_Open()
    with sheet4
       Set trgt = .Range("D4:F500")
       with .range("d4")
           .value = .value
       end with
    end with
End Sub

================
You could call the worksheet_Change event in sheet4 IF you make a change to the
procedure statement:

Private Sub Worksheet_Change(ByVal Target As Range)
becomes
Sub Worksheet_Change(ByVal Target As Range)

And the code would look like:

Private Sub Workbook_Open()
    with sheet4
       Set trgt = .Range("D4:F500")
       Call .Worksheet_Change(Target:=.Range("d4"))
    end with
End Sub


    


Philosophaie wrote:
> 
> "Bernard Liengme" wrote:
> 
> > This code works
> >
> > Sub Worksheet_Change(ByVal Target As Range)
> >   If Not Intersect(Target, Range("D4:F500")) Is Nothing Then
> >     MsgBox "Cell in D4:F500 has be changed"
> >   End If
> > End Sub
> >
> > Remember it must be added to the Sheet model not the Genaeal module
> > One way is to right click the sheet's tab and select View Code
> >
> > best wishes
> > --
> > Bernard Liengme
> > Microsoft Excel MVP
> > http://people.stfx.ca/bliengme
> 
>  I already have this in my code:
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>     Dim trgt As Range
>     Dim intersect As Range
>     Set intersect = Application.intersect(trgt, Target)
>     If Not intersect Is Nothing Then
>         With Sheets("Sheet4")
>         For n = 2 To 500
>             Cells(n, 5) = .cell((n - 1), 5) + .cell(n, 6) - .cell(n, 4)
>         Next n
>         End With
>      End If
> End Sub

-- 

Dave Peterson
0
Dave
1/25/2010 4:07:11 AM
For the nth time, I will repeat that the Worksheet_Change event
procedure must NOT (!!!!!!!) be in the ThisWorkbook code module. If it
is, it will not be recognized by VBA and will not run when a cell
value is changed. The Worksheet_Change event MUST (!!!!!!) go in the
sheet module of the worksheet whose changes are to trapped.  In Excel,
right-click on the tab of the sheet whose changes you want to trap,
and choose "View Code". Put your that code module. Again, do NOT (!)
put the Worksheet_Change event code in the ThisWorkbook module. 

Maybe you don't understand what events are. See
http://www.cpearson.com/excel/Events.aspx for much more detail about
events, how they work, and how to code them. 

Finally, again, do NOT put the Worksheet_Change event in the
ThisWorkbook module. This will NOT work.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]



On Sun, 24 Jan 2010 17:59:01 -0800, Philosophaie
<Philosophaie@discussions.microsoft.com> wrote:

>You have all my code.  Why can't I figure out why the Worksheet_Change is not 
>firing when a cell in the range is manipulated?  I do save and exit the 
>workbook before I run the updated program and I am using a xls file in Excel 
>2007.
0
Chip
1/26/2010 4:49:19 PM
Chip
Remember the Paul Newman movie "Cool hand Luke" and the guard said to Luke, 
"What we have here is a failure to cummunikate (sp)
-- 
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett@gmail.com
"Chip Pearson" <chip@cpearson.com> wrote in message 
news:5u6ul512jvjvucp2pe924bulrfdduecuf5@4ax.com...
> For the nth time, I will repeat that the Worksheet_Change event
> procedure must NOT (!!!!!!!) be in the ThisWorkbook code module. If it
> is, it will not be recognized by VBA and will not run when a cell
> value is changed. The Worksheet_Change event MUST (!!!!!!) go in the
> sheet module of the worksheet whose changes are to trapped.  In Excel,
> right-click on the tab of the sheet whose changes you want to trap,
> and choose "View Code". Put your that code module. Again, do NOT (!)
> put the Worksheet_Change event code in the ThisWorkbook module.
>
> Maybe you don't understand what events are. See
> http://www.cpearson.com/excel/Events.aspx for much more detail about
> events, how they work, and how to code them.
>
> Finally, again, do NOT put the Worksheet_Change event in the
> ThisWorkbook module. This will NOT work.
>
> Cordially,
> Chip Pearson
> Microsoft MVP 1998 - 2010
> Pearson Software Consulting, LLC
> www.cpearson.com
> [email on web site]
>
>
>
> On Sun, 24 Jan 2010 17:59:01 -0800, Philosophaie
> <Philosophaie@discussions.microsoft.com> wrote:
>
>>You have all my code.  Why can't I figure out why the Worksheet_Change is 
>>not
>>firing when a cell in the range is manipulated?  I do save and exit the
>>workbook before I run the updated program and I am using a xls file in 
>>Excel
>>2007. 

0
Don
1/26/2010 7:39:42 PM
Reply:

Similar Artilces:

3 challenges; concequences of activating cell + 2 text formula problems
Hey guys - sorry I slipped on the send-button.... I have some minor issues on my board: 1) Are there any way to delete the cell value in range A1 when activating cell/range A2? - wanted effect is when user activates cell A2 (by click) the value / content inn cell A1 will be deleted. 2) When constructing coordinates as textstrings (X Y Z) by linking values from range B1, B2 and B3 ( the text string will not appear whith 1 decimal format in all positions - not where the value is an integer. Ex: B1=1 B2=2.5 B3=3.4 the string shows: "1 2.5 3.4", but I want it to show: "1.0 2.5 3.4&q...

Media player in active X
Quick question if I may... I need to play two simultaneous sound files in Excel, and as I cannot have two userforms at the same time, I'm trying to have one play as a userform in mediaplayer and the other play as a windos media player ActiveX object embedded into the sheet (using Office 2007). However when I try to instruct the windows media player to begin playing it's file it hits a bug and refuses to budge. Am I attempting the impossible or just missing the obvious? Many thanks Private Sub UserForm_Activate() Dim Mfile As String Mfile = "c:\video\crowd.wav" MP....

Activity Tracking of deleted batches
Hi - Is it possible to use Activity Tracking to see who deleted batches? If yes which options should be selected? Thanks! Michael, Yes. You can track. In Activity Tracking setup, Select File Tracking as Activity Tracking Type and Mark Deletions from Transactions FIles, Select the Users you'd like to track. Now, whatever batches get deleted, you can view the Activity Tracking report who has deleted it. -- Thanks Janakiram M.P. MCP-GP Note: If you are a Microsoft Partner, You can also Login to Dynamics GP partner forums at http://social.microsoft.com/Forums/en-US/partnerdynamic...

COUNTIF using 2 ranges??
OK, here's the deal. I have a worksheet with about 500 rows of data, o each row has a unique ticket number, a type code (1-9) and a compan name (about 20 different companies) What I need to do is get th results for each company and each code. Therefor, the number o tickets that belong to company XYZ that have a code of 3 would b entered into one field. Then 4, 5, 6 etc. I have tried COUNTIF and array formulas but have has no success. I you have any ideas, please share. Thank -- Message posted from http://www.ExcelForum.com Snickstx, a pivot table will give you what you are looking...

Outlook 2003 Target Path
We have XP Pro with Outlook 2003. I need to know the Target Path for Outlook but at the present this is greyed out and can't be altered. How do I find this?? Are you asking the file path for the Outlook.exe file? Usually it's at C:\Program Files\Microsoft Office\OFFICE11 If that's not what you're asking, please rephrase your question. -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:FD896CAC-6A19-4183-AE5C-CCBF34403336@microsoft.com, Gordon wro...

Dynamic series range from AutoFilter
Hi All...... I have code below, that after I autofilter and copy results over to another sheet, will go to AA9 and AE9 as the first cells in Series1 and Series2 sources...that works fine, except I never know where the bottom of the column is going to be based on what data is filtered.....when I try to Record the macro, I only get fixed references....AA9 and AE9 are fine for the top of the column, but I need somehow to "find" the bottom of the column....... Existing code: Sub M034Chart() ' ' M034Chart Macro ' Macro recorded 04/12/2005 by CLR ' ' Sheets...

help defining dynamic range
To all, I have a mean line that is defined as a name as follows, where C2 is the mean value ='FINAL Averages'!$C$2*ROW(ChartLabelsHeat2)/ROW(ChartLabelsHeat2) This creates a dynamic mean line based on the data in column A of sheet - Data. The name chartlabelsheat2 is defined as: =OFFSET(DATA!$A$2,1,0,COUNTA(DATA!$A:$A)-1,1) where column a contains my x-axis data, which totals some 400 points. This works fine, however I want the mean line to start at row 100 of column a, i.e. 100 data points (A100) into the chart. Is there any easy way I can do this using the formulae that I ha...

How can I get the mode of a range?
I would like to know the most frequently occurring number in a range on my spreadsheet, but =mode(c4:u4) is invalid. It looks like it wants =mode({1,3,1,5,... etc.). How can I convert my range into a form the Mode function likes? TIA LAS Never mind. I discovered that I get this error if I don't have three or more numbers as data in the range. I had put the function in before I filled in all my data. "Laurel" <FakeMail@Hotmail.com> wrote in message news:O$mE4lrsIHA.3780@TK2MSFTNGP03.phx.gbl... >I would like to know the most frequently occurring number in a...

Charting Variable Ranges
I have an xls which contains money saved tracked against=20 month and year it takes place. This is then charted. i.e: Jan 04 =A310,000,000 Feb 04 =A3120,000 As time passes, more months end up on the base. I had=20 hoped that by using a variable range name, I'd be able to=20 get the charts to automatically update, e.g. define a=20 range for the series data as: =3DINDIRECT("Sheet1!B1:B"&COUNTA($B:$B)) (call it varSavedByDate) and then have the chart refer to the range by name. Unfortunately this doesn't seem to work. Any bright ideas? you ca...

VBA Range Formula
Can anybody tell me how to get this to work? In other words, how do I reference my procedure variables in a cell formula? Thanks! Sub Marktest() Set b = Cells.Find("Total", , xlValues) Set c = Range("C5") Range(b.Offset(-1, 2), c).Formula = _ "=sumproduct(($B5=Range(b.Offset(-7, 1), b.Offset(-5, 1)))*$C$1:$C$3)" End Sub I'd do something like: Option Explicit Sub Marktest2() Dim myRng As Range Dim b As Range Dim c As Range With ActiveSheet Set b = .Cells.Find("Total", , xlValues) Set...

Groups in Active Directory
I have group of people in a OU named processing. My Processing Manager is asking to use that address publically, or processing@mydomain.com, and access to the mailbox be granted to all of our processors. Secondly, this processing OU is also used for a security group that has permissions all over the network. Is there a way to get around this or do I have to recreate the OU with a slightly different name and then create and user called processing? Any help appreciated. Hi Chuy There is no problems having a User or Group with the same name as an OU. You say this OU is being used for...

why it not writing to target files?
I have modified a code from this NG to help me copy my nick into a number of *.xls files in a spesific DIR. But when activated it only copies my nick to the file where the macro resides and not on the "target" files. I know I'm doing something wrong here with this "active document" part, but can't figure what the correct syntax should be. Any help is appreciated... -------------------------------- Sub Insert() Dim mesaj As String Dim FilesArray() As String, FileCounter As Integer Dim FName As String, LoopCounter As Integer FName = Dir("c:\my doc...

How do I break a date range by month?
I am trying to calculated the days in a month for a 14 day pay period. What I want to do is to calculate how many days are in each month if the pay period crosses a month. For example the begining date is 8/21/05 and it ends 9/3/05. I was wondering if there is a formula that will allow me to calculated that there are 3 days in Sept and 11 in August. Thanks =DATE(YEAR(start),MONTH(start)+1,0)-start+1 and =end-DATE(YEAR(end),MONTH(end),0) -- HTH RP (remove nothere from the email address if mailing direct) "HeatherDawn" <HeatherDawn@discussions.microsoft.com> wrote...

variable range
I have a variable n. I want to select a printarea using n. like (50,1:n,8) I can't see how you do that since selecting ranges only uses the "B1" type of referance for cells, not the (1,2) style. John Range(Cells(r1, c1), Cells(r2, c2)), where: (r1,c1) are the row,col of the first cell and (r2,c2) are the row, col of the last cell. From your query, I suppose r2 =n-th row? -- protonLeah ------------------------------------------------------------------------ protonLeah's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=32097 View this threa...

Printing a range of invoices for 1 customer
Hello, I've been vexed by this a lot over the years, so thought it was time to make sure I wasn't missing something obvious. When a customer's account goes overdue, or one day they decide they want to "settle", is there a way to (in one fell swoop) print all the open transactions for that customer? I don't mean just the summary you can get from Receivables Transacting Inqury--Customer. I mean I want to print each open SOP document complete with all the line details (without opening each one, printing, closing, and going to the next one and repeating). Any a...

SOLUTION: copy/move selected shortcut targets to a destination you choose on the fly
Hi All, I was searching all over in relation to this question. I would have added some links to older relevant threads that I found, but there is a 60 day limit. Although the below tags link to a member-site (popular), this is really not meant to come across as advertising.. I had been looking for some time into this similar issue, and information on the net seems not only spotty and incomplete, but the solution is very elusive... and it shouldn't be. What's really tough to find is way to select objects at random, say by control-clicking any files and shortcuts in a fold...

Copy to Named Range
I can "Set Range" then "Copy" then "select" then paste as below If Cells(CCC - 1, 1).Value = Cells(CCC, 1).Value Then Set MyRange = Cells(CCC, 100).End(xlToLeft).Offset(0, 1) Range(Cells(CCC - 1, 11), Cells(CCC - 1, 14)).Copy MyRange.Select ActiveSheet.Paste For speed I do not want donot want to select but the following code errors out Range(Cells(CCC - 1, 11), Cells(CCC - 1, 14)).Copy Range(Myrange) -- Message posted via http://www.officekb.com Just off the top of my head, you need quote marks around the range's name in the Range cal...

some 'sum' formulas when double clicked to check range zero out
I use excel spreadsheets for cost reporting. I have a summary page an 35 separate schedule pages. the schedules have columns showing cost t date, cost to complete, budget, over/under spend etc. the totals ar linked back to the summary page. the summary page shows these total and then at the bottom adds them all up to show the overall costs. It these formulas that are acting strangely. Even though the figure above change when new figures are entered into the schedules, they d not. And, when I double click on these formulas to check the rang then press return, the cell goes blank or show...

Inventory query with time range
I am creating an inventory database. Inventory is taken 3 times per day based on shift start. I want to be able to create a parameter query and then a report that will show the inventory taken at the start of each shift. There are nine areas that are inventoried each shift. I need to be able to query by date and time. I want the times to be broken down by shift name (i.e. Days, Afternoons, Nights). The form that feeds the table where the query is run has a txtDate field and txtTime field with default values of =Now() so that when the data is saved to the Inventory table the time and date...

Changing Account owner also changes contact owner (and activity owner)
Hi, When I change the owner of an account, CRM also changes the owner of the contacts within that account and changes the owner of the activities that are connected to the contact. Is this behavior by default? (I can't Imagine). Sanne This is the default behavior. This is a nice feature when you lose an employee and need to reassign that employees accounts and all their sub-records to a replacement. Have you tried sharing the account record in question? What is your goal by reassigning the account but not the other related records? - Scott www.easiintl.com Sanne wrote: > Hi, >...

GP Activity Table
I got advice off this website to delete a record from the Activity table. (Error msg was "Someone is changing the Account Format. You cannot log into this company.")I went to SQL Server, and looked through the views and the tables, but could not find any table with the name Activity or anything looking like it. Can you tell me the name of the table? Thanks - I'm new to GP. We just installed 10.0. I have not yet upgraded to GP10.0 and am still on 9.0 but I would expect the setup to be similar. The activity table, at least for us with 9.0, is in the DYNAMICS database as op...

How do i sort a range and place the results in another range without using Macros?
I have a worksheet that has a table of data. The table spans cells B2 - Q72 I would like to place the same table of data underneath it but sorted by one of the colums in Ascending Order, then the same again underneath that but this time sorted in Descending order. eg: | |Table of Data | | |Table sorted in Ascending Order | | |Table sorted in Descending Order | so when data is changed in the top table the sort orders in the tables underneath are reflected as well. I would like this to happen automatically when data is entered and not by manually running a macro to update all the time? ...

VBA Identifying Active Conditional Formatting
Is there a way to identify if conditional formatting is being applied to a given field? I have a form where I'm identifying missing mandatory data by using conditional formatting to turn the field red. However, I have a "Reviewed" field that needs to be checked after the data has been verified by the user, and I want to code a quick check to make sure none of the mandatory data is missing before allowing them to move on. The list of mandatory fields is conditionally linked to another field on the record (ie: if you buy a product, you have to supply a vendor name; if you make i...

Inclusive Date Range
I'm creating a spreadsheet to help calculate the split on rent and utilities. The method I'm using to split things up requires a calculation using the number of days in a billing period. Let's say A2 holds the period start date and B2 holds the period end date. June 1 - June 30 will be our arbitrary billing period. That period contains 30 days, but using a formula like this =B2-A2 would return 29 days because it's calculating the difference, whereas I want the inclusive span. What I ended up doing was changing the formula to =B2-A2+1 which works, but I'm wondering if ther...

Active/Inactive
Hi there.. i got this report the displays trainees with no training history and they need to be scheduled for training. with this code, it works fine. SELECT Trainees.TraineeID, Trainees.FirstName, Trainees.LastName, Trainees. JobTitle, Trainees.ContractorName, Trainees.Language, Trainees.TraineeStatus FROM Trainees WHERE (((Trainees.TraineeID) Not In (SELECT DISTINCT TraineeID FROM [Training Records]))); now, i added a TraineeStatus field on Trainees table with a lookup data so the user can make a selection from Active, Inactive & OnVacation. Now, what the user wan...