Worksheet event help

Have the following in a sheet code that i would like to activate when
the enter key is pressed in B5.

tried the following, but no success, the 1st part i was hoping would be
the change event that would run a sorting macro in the 2nd part.  any
help???


(1st Part)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address(0, 0) = "b5" Then




(2nd Part)

    Application.ScreenUpdating = False
    ActiveWindow.FreezePanes = False
    Range("A4").Select
    ActiveWindow.SmallScroll Down:=234
    Range("A4:B273").Select
    Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
    Range("A3").Select
    Sheets("LookupLists").Select
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E300")
    Range("E2:E300").Select
    Sheets("Master").Select
    ActiveWindow.SmallScroll Down:=-246
    Range("A4").Select
    ActiveWindow.FreezePanes = True
    Application.ScreenUpdating = True
    End If
End Sub

0
1/18/2007 10:08:57 PM
excel 39879 articles. 2 followers. Follow

3 Replies
359 Views

Similar Articles

[PageSpeed] 41

Unless you have
Option Compare Text
at the top of the module, this line:
If Target.Address(0, 0) = "b5" Then
will never have the True portion followed.

Try:
If Target.Address(0, 0) = "B5" Then

Personally, I like this test better:

if target.cells.count > 1 then exit sub
if intersect(target, me.range("b5")) is nothing then exit sub

I find it easier to change (to include more cells, for example).

(I didn't look any further.)

K1KKKA wrote:
> 
> Have the following in a sheet code that i would like to activate when
> the enter key is pressed in B5.
> 
> tried the following, but no success, the 1st part i was hoping would be
> the change event that would run a sorting macro in the 2nd part.  any
> help???
> 
> (1st Part)
> 
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
>     If Target.Address(0, 0) = "b5" Then
> 
> (2nd Part)
> 
>     Application.ScreenUpdating = False
>     ActiveWindow.FreezePanes = False
>     Range("A4").Select
>     ActiveWindow.SmallScroll Down:=234
>     Range("A4:B273").Select
>     Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
> Header:=xlGuess, _
>         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
>         DataOption1:=xlSortNormal
>     Range("A3").Select
>     Sheets("LookupLists").Select
>     Range("E2").Select
>     Selection.AutoFill Destination:=Range("E2:E300")
>     Range("E2:E300").Select
>     Sheets("Master").Select
>     ActiveWindow.SmallScroll Down:=-246
>     Range("A4").Select
>     ActiveWindow.FreezePanes = True
>     Application.ScreenUpdating = True
>     End If
> End Sub

-- 

Dave Peterson
0
petersod (12005)
1/18/2007 10:29:43 PM
Dave

Thanks

> if target.cells.count > 1 then exit sub
> if intersect(target, me.range("B5")) is nothing then exit sub


Worked a treat.
Never even thought about text case, appreciate your assistance and
comments



Steve









Dave Peterson wrote:
> Unless you have
> Option Compare Text
> at the top of the module, this line:
> If Target.Address(0, 0) = "b5" Then
> will never have the True portion followed.
>
> Try:
> If Target.Address(0, 0) = "B5" Then
>
> Personally, I like this test better:
>
> if target.cells.count > 1 then exit sub
> if intersect(target, me.range("b5")) is nothing then exit sub
>
> I find it easier to change (to include more cells, for example).
>
> (I didn't look any further.)
>
> K1KKKA wrote:
> >
> > Have the following in a sheet code that i would like to activate when
> > the enter key is pressed in B5.
> >
> > tried the following, but no success, the 1st part i was hoping would be
> > the change event that would run a sorting macro in the 2nd part.  any
> > help???
> >
> > (1st Part)
> >
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> >     If Target.Address(0, 0) = "b5" Then
> >
> > (2nd Part)
> >
> >     Application.ScreenUpdating = False
> >     ActiveWindow.FreezePanes = False
> >     Range("A4").Select
> >     ActiveWindow.SmallScroll Down:=234
> >     Range("A4:B273").Select
> >     Selection.Sort Key1:=Range("A4"), Order1:=xlAscending,
> > Header:=xlGuess, _
> >         OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
> >         DataOption1:=xlSortNormal
> >     Range("A3").Select
> >     Sheets("LookupLists").Select
> >     Range("E2").Select
> >     Selection.AutoFill Destination:=Range("E2:E300")
> >     Range("E2:E300").Select
> >     Sheets("Master").Select
> >     ActiveWindow.SmallScroll Down:=-246
> >     Range("A4").Select
> >     ActiveWindow.FreezePanes = True
> >     Application.ScreenUpdating = True
> >     End If
> > End Sub
> 
> -- 
> 
> Dave Peterson

0
1/18/2007 10:44:13 PM
Personally,
I find coding like

If (Target.Row = 5) and (Target.Column = 2) Then

less error prone




Dave Peterson wrote:
> Unless you have
> Option Compare Text
> at the top of the module, this line:
> If Target.Address(0, 0) = "b5" Then
> will never have the True portion followed.
> 
> Try:
> If Target.Address(0, 0) = "B5" Then
> 
> Personally, I like this test better:
0
1/19/2007 7:39:16 PM
Reply:

Similar Artilces:

Event 9646
E2K3 SP1; Mixed Mode I received a few of these in my Application Log on the E2K3 server, but I cannot find any information regarding them: Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9646 Date: 1/11/2005 Time: 10:30:51 AM User: N/A Computer: MXMA2 Description: Mapi session "/o=Org/ou=Site/cn=Recipients/cn=RMonti" exceeded the maximum of 32 objects of type "session". The MS KB Article http://support.microsoft.com/default.aspx?scid=kb;en-us;830836&sd=ee has the same Event ID, but does not reference the object of type "session&...

Help making BINGO cards
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I am quite the novice at using Excel. I'm trying to create a set of BINGO cards for a church group and I'm having trouble figuring it out. I saw a post about something called RAND function but I don't know what that means. <br><br>What I need is 56 unique cards with 5 columns and 5 rows each. The cards will be using the numbers 1-24 leaving the center square blank or &quot;0&quot;. <br><br>Can anyone please assist? This is a multi-part message in MIME format. ----...

formula help #8
The following formula queries and totals information on a specified sheet. In this case the specified sheet name is 2003 I have several sheets (2001, 2002, 2003, 2004.....) I would like to have the value for the sheet name in the formula reference a cell in which I could type in the name of the sheet I want to query. Can someone tell me how to accomplish this. Just changing the '2003' in the formula to the reference cell doesn't work. (FYI - the reference cell will be L1) =SUMIF(('2003'!H2:I400),+(H3),('2003'!D2:I400)) TIA Mike =SUMIF((INDIRECT("'...

Error message in event log
Server running Exchange 5.5 SP4 and W2K. I get the following error meesage in the event log. Event Type: Error Event Source: MSExchangeIS Private Event Category: Rules Event ID: 1147 Date: 3/12/2005 Time: 2:07:19 PM User: N/A Computer: RIEXCH06 Description: Error --- while disabling rule on public folder with rule ID 21. The folder ID of the public folder is in the data section of this event. How can I see which public folder is using rule id 21 ? Or does anyone have seen this error before ? -- JDA ...

help with disp interfaces
I'm familiar with COM concepts, but haven't programmed it in a few years and never really got into dispatch interfaces. I'm trying to get at the DHTML in a page being displayed in a CHtmlView (trying to display an MRU). I've overloaded OnDocumentComplete(), used GetHtmlDocument to get a dispinterface to my DHTML Document object, and want to use the getElementById() method on that. To call that method, I'm first using GetIDsOfNames() to get the dispid for that method and its parameters, then calling Invoke(). Phew!! I've struggled partway through, I can get the dispi...

Negative Numbers in Brackets
Hi, I have read every questions and replies on this site in order to get my negative numbers to show in brackets and nothing works. I am desparate. The regional settings are changed to indicate negative numbers in brackets but when I try formatting the cells in Excel, I cannot get the numbers to show in brackets. I have Windows XP, Office 2003. Please help me... Thanks. Are they seen as numbers at all? Widen the cell, don't force any aligment, are the numbers left or right aligned, if they are left they are seen as text you can also try =ISTEXT(cell) if it returns TRUE they a...

Hide the Help button from Propertysheet dialog
Hello, Could you tell me, how can I hide the Help button from Propertysheet dialog in the wizard mode? I want to appeare just Back, Next, Cancel and Finish button in the dialog. I don't need to the Help button. By the way, PropertySheet.m_psh.dwFlags &= ~PSH_HASHELP; doesn't work. Thank you so much, Monica. Hello Monica, > By the way, PropertySheet.m_psh.dwFlags &= ~PSH_HASHELP; doesn't work. This should work. You need to remove it from each of the property pages as well. Here is the link: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vccor...

Customized Help Content
If you have multiple organizations on the same deployment, how can you customize the help content for each organization. The reason that I would want to do this is that each organization might have a different layout on the forms and different fields that the users are supposed to track. The help files are all stored as phyical files on the server so I am not sure how to make them organization specific. Thanks, Dan ...

Calculating Averages
Hi, I am using the newest version of Excel on Windows 7, and I need some help with a complex calculation. In cells A1-A100, I have dollar amounts ranging from $1-$1000. In cells B1-B100, I have percentages ranging from 0%-100%. I want to break out the cells in A1-A100 in several groups, like follows: $1-$99, $100-$199, $200-$299, and so on up to $1000. Then, I need to calculate the average percentage for jobs in those categories. So, for the category of $1-$99, lets say there are two cells with amounts in that range, A1 and A2. Their percentages in B1 and B2 are 40% and 6...

on_change() event for text fields
Is there really no way to assign an event to a text field? We want to be able to have the format saved correctly for phone numbers when they're entered in (###)###-#### I've read some threads about this already and it looks like it cannot be done unless you write an app to constantly check and replace the phone numbers within the database itself. I can do this but it would be nice to know if there was any simpler way of going about this. Thanks in advance. nope, not at all,.....only picklists its kinda stupid too because under text boxes they have the ability to select &qu...

HELP!!! #4
My computer crashed... I'm recovering my files from my hard drive, but I never did backups to save my email. I had thousands of emails in Outlook... is there any way to retrieve those? By the way, I had to connect my old hard drive to another PC and access my hard drive in that manner. >-----Original Message----- >My computer crashed... I'm recovering my files from my >hard drive, but I never did backups to save my email. I >had thousands of emails in Outlook... is there any way to >retrieve those? >. > Look for file(s) with a *.PST extension. Location...

Help Query.
Hi, I have 2 tables, one it has customers who are past due and another table that has customers who have paid and not past due after a certain period. My objective is to find the customers who hasn't paid yet. I did a query, joined by account #, this query will return customers who has paid after they were past due. But I am looking for account IDs of customers who are still past due. Anyway to do this? Thansk for the help! Try the query wizard about finding unmatched rows. It seems you need rows from the first table that are NOT in the second table. Hoping it may help, Vander...

Formula Help
Hi Folks: I have a row of 6 columns to add (E thru J) The values of each cell goes from 0 to 5 in 0.5 increments. If the value of E is less than 1.5, I'd like to just do s straight addition (max value 30.0) If the value of E is greater than 2.0, then I would like to double the added value of get 60.0 What I'm using is =IF(E12<2,,(E12+F12+G12+H12+I12+J12)*2) which works. . . However, if E12 is under 2.0, then it evaluates to 0 instead of 30 Any help wd be appreciated - Thanks Maybe... =MAX(30,(SUM(e12:j12)*2))*(1+(e12>2)) or =MAX(30,(SUM(e12:j12)*2))*if(e12>2,2,1...

Opening help starts RMS installation
Whenever I try to open help on any office or windows program the RMS installer executes. Any ideas? Happened to me, too. What I did is let the dialog go through it's motions, then when it asks for the install disc put it in the drive and find the file - you may have to search the disc for the file first so you know where it is. This usually only hapopens once, but keep the install disc handy because I had to do the same thing when I clicked "Help" for Excel, Quickbooks, and a few others. It wasn;t asking for those install files, it still wanted the RMS install files. ...

Outlook 2003 just closes down by itself
Hi, Outlook 2003 (SP3, all updates) goes along happily, then just closes down by itself. This behaviour seems to have happened since the Microsoft April security updates, but that might not have anything to do with it. Any one have any pointers I can explore please? Thanks, Steve Maincat wrote: > Hi, > > Outlook 2003 (SP3, all updates) goes along happily, then just closes down by > itself. This behaviour seems to have happened since the Microsoft April > security updates, but that might not have anything to do with it. What happens if you load Outlook in its safe...

Marco help
Hi all gurus, below is code for a button so once pressed it will send an email to the relevant people and information is contained within the email. What i now want it to do is add an hyperlink to the location of the files so the user can press on the link and open a folder on the server. The file path is: M:\Procurement\Sub Contract Submissions Hope this is possible!! If Response = vbYes Then Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) strbody = "Sub Contract Submissions...

Help! Search & Replacing time formats
Hi I have a whole spreadsheet full of fields similiar to the following 10:00AM 12:15PM 2:25PM 4:40PM 7:00PM 9:20PM 10:00PM 12:20PM 2:25PM 4:30PM 7:15PM 10:00AM 12:30PM 2:45PM 9:15PM 10:40AM 1:10PM 5:00PM 7:20PM 9:35PM etc. I need to convert all times to 24 hour, and drop the AM & PM. The later is easy, but how do I do a quick Search & Replace without incorrectly converting 10:**AM to 22:** etc. Any tips would be appreciated. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly fro...

Formaula for less than date and name begins with...!!! HELP!!!
IM trying to create a formula that gives me a total count for cells that are less than 02/05/10 and the name begins with FHLM, heres what i came up with... =COUNTIF(E:E,">="&DATE(1900,1,1))-COUNTIF(E:E,">="&DATE(2010,2,5))+COUNTIF(E:E,DATE(2010,2,5))-COUNTIF(U:U,"FHLM*") I keep gettin a bunch of ###### or 0, depending on how I move the data around.. Any ideas???? =SUMPRODUCT(--(E1:E100<=DATE(2010,2,5)),--(LEFT(U1:U100,4)="FHLM")) "Senor Martinez" wrote: > IM trying to create a formula that gives me...

Help needed #3
Many thanks to JE McGimpsey Solved my problem -- okane ----------------------------------------------------------------------- okanem's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=930 View this thread: http://www.excelforum.com/showthread.php?threadid=27656 ...

I'm a newbie please help with my function
I have an excel spreadsheet that has 2 columns of numbers side by side. I want a formula that counts the number of times the number in colum one are larger than the number that is right beside it in column two. After it counts it I want to display the total number that are large in a seperate cell. Is this possible. I cruised around the forum an I did not see what I was looking for so any help would be appreciated -- colincanno ----------------------------------------------------------------------- colincannon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26...

Urgent!Need help with creating a Christmas PowerPoint slideshow
I'm creating a Christmas PowerPoint slide show of my family,can someone tell me where I can download free Christmas templates for my presentation? And I want to put this PowerPoint slideshow on Youtube to share with all my relatives and friends. I tried to upload an.PPT file to Youtube but failed, any idea? Thanks Dwight, There are some templates on office online: http://office.microsoft.com/en-us/templates/results.aspx?qu=CHRISTMAS&sc=4&av=ZPP -- Luc Sanders MVP - PowerPoint "Dwight" <Dwight@discussions.microsoft.com> schreef in bericht new...

550 Errors HELP!!!!
With one particular client that we send email to, thier end keeps bouncing ALL emails from us. The NDR comes back as- MTP 550 error <EXCHANGESERVER.domain #5.5.0 smtp; 550 Hacked HELO: you are not EXCHANGESERVER.domain>. Sounds like most likely they are doing reverse lookups, and the fqdn presented in the HELO command does not match the PTR record, or you have a missing PTR record. Run a report of your domain at dnsreport.com and it should highlight any problems you have. Post back with what those problems are and we can help identify how to fix them. -- Ben Winzenz Exchange...

MSExchangeIMC
If anyone can help me solve this I would appreciate it. It seems several people are having difficulties with Event id: 4118, but as of yet this is all I have been able to come up with. I know the error is in the DATA expressed in WORDS. Event Type: Error Event Source: MSExchangeIMC Event Category: (6) Event ID: 4118 Date: 12/16/2003 Time: 10:50:09 AM User: N/A Computer: ExchangeServer Description: The description for Event ID ( 4118 ) in Source ( MSExchangeIMC ) could not be found. It contains the following insertion string(s): ZAWS9Z3M. Data: 0000: 00010227 80004005 MAPI Error Code ...

event 3092
I have a customer who is receiving the following warning in Event Viewer on their Exchange 2003 server. Source: MSExchangeIS Public St Category: Replication Errors Event ID: 3092 Description: Error 1129 occurred while processing a replication event. Folder: (2-FFFFFFF0003) IPM_SUBTREE\Internet Newsgroups Any ideas on how to get rid of this warning? They only have one Exchange server. They did remove an Exchange 5.5 box and followed the KBs to do so. Everything works fine. They would like to get rid of these warnings. Any ideas are greatly appreciated. DF Are there any server...

Conditional Format Help Needed
I would like to apply a Conditional Format based on two factors. Currently I have two different Conditions set up. Condition 1: =IF(IF($G2="Ready to Publish",1,0)=1,1,0) This format successfully works to gray out and cross out text that matches "Ready to Publish." Condition 2: =MOD(ROW()-1,2)=0 This format applies alternating row fills for easier viewing. While both of these formulas work, my problem is Condition 1 overrides Condition 2 when "Ready to Publish" is detected. Is there a way to combine the two formulas so they wouldn't overr...