Using Target.Address in Excel 98

Hi
I have a code which as Target.Address which works in Excel 2000 but will not 
work in excel 98.

Does anyone know what code to use for when a workbook change is to take 
affect.

The current code which works in Excel 2000 is below:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$26" Then
    If Range("G26") = "Other" Then
    ActiveSheet.Unprotect
    Sheets("Recording Sheet").Range("E28") = "Please speficy:"
    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2
    Sheets("Recording Sheet").Range("G28:I28") = ""
    Sheets("Recording Sheet").Range("G28:I28").Locked = False
    ActiveSheet.Protect
    Sheets("Recording Sheet").Range("G28:I28").Select
    ElseIf Range("G26").Value = "" Then
    ActiveSheet.Unprotect
    Sheets("Recording Sheet").Range("E28") = ""
    Sheets("Recording Sheet").Range("G28:I28") = ""
    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
    Sheets("Recording Sheet").Range("G28:I28").Locked = True
    ActiveSheet.Protect
    Else
    ActiveSheet.Unprotect
    Sheets("Recording Sheet").Range("E28") = ""
    Sheets("Recording Sheet").Range("G28:I28") = ""
    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
    Sheets("Recording Sheet").Range("G28:I28").Locked = True
    ActiveSheet.Protect
    End If
    End If
End Sub

Any help would be appreciated.

Thanks
Noemi
0
Noemi (23)
11/7/2005 7:39:59 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
662 Views

Similar Articles

[PageSpeed] 46

You would have a better chance of getting help if you tell us exactly where 
the code breaks down.

One no-no you've commited though is not turning off events in the sheet 
change event handler when your code is itself making changes to the sheet. 
This causes an endless loop in theory.  So add an Application.EnableEvents = 
False before making a sheet change, and set the same to True after your last 
sheet change.

-- 
Jim
"Noemi" <Noemi@discussions.microsoft.com> wrote in message 
news:28F92323-F201-4D93-A127-9B04BC803AF8@microsoft.com...
| Hi
| I have a code which as Target.Address which works in Excel 2000 but will 
not
| work in excel 98.
|
| Does anyone know what code to use for when a workbook change is to take
| affect.
|
| The current code which works in Excel 2000 is below:
|
| Private Sub Worksheet_Change(ByVal Target As Range)
|    If Target.Address = "$G$26" Then
|    If Range("G26") = "Other" Then
|    ActiveSheet.Unprotect
|    Sheets("Recording Sheet").Range("E28") = "Please speficy:"
|    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2
|    Sheets("Recording Sheet").Range("G28:I28") = ""
|    Sheets("Recording Sheet").Range("G28:I28").Locked = False
|    ActiveSheet.Protect
|    Sheets("Recording Sheet").Range("G28:I28").Select
|    ElseIf Range("G26").Value = "" Then
|    ActiveSheet.Unprotect
|    Sheets("Recording Sheet").Range("E28") = ""
|    Sheets("Recording Sheet").Range("G28:I28") = ""
|    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
|    Sheets("Recording Sheet").Range("G28:I28").Locked = True
|    ActiveSheet.Protect
|    Else
|    ActiveSheet.Unprotect
|    Sheets("Recording Sheet").Range("E28") = ""
|    Sheets("Recording Sheet").Range("G28:I28") = ""
|    Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
|    Sheets("Recording Sheet").Range("G28:I28").Locked = True
|    ActiveSheet.Protect
|    End If
|    End If
| End Sub
|
| Any help would be appreciated.
|
| Thanks
| Noemi 


0
jrrech (1932)
11/7/2005 1:35:54 PM
I know of no excel 98. Please clarify. I just tested
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$2" Then MsgBox "Hi"
End Sub
in xl97sr2 on xp home and it worked just fine.
is recordingsheet your active sheet?
=====something like this would be cleaner
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$G$26" Then Exit Sub
 With Target.Worksheet
  .Unprotect
 If UCase(Target) = "OTHER" Then
    .Range("E28") = "Please speficy:"
    .Range("G28:I28").Interior.ColorIndex = 2
    .Range("G28:I28") = ""
    .Range("G28:I28").Locked = False
  Else
   .Range("E28,G28:I28") = ""
   .Range("G28:I28").Interior.ColorIndex = 1
   .Range("G28:I28").Locked = True
 End If
  .Protect
 End With
End Sub



-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Noemi" <Noemi@discussions.microsoft.com> wrote in message
news:28F92323-F201-4D93-A127-9B04BC803AF8@microsoft.com...
> Hi
> I have a code which as Target.Address which works in Excel 2000 but will
not
> work in excel 98.
>
> Does anyone know what code to use for when a workbook change is to take
> affect.
>
> The current code which works in Excel 2000 is below:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>     If Target.Address = "$G$26" Then
>     If Range("G26") = "Other" Then
>     ActiveSheet.Unprotect
>     Sheets("Recording Sheet").Range("E28") = "Please speficy:"
>     Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 2
>     Sheets("Recording Sheet").Range("G28:I28") = ""
>     Sheets("Recording Sheet").Range("G28:I28").Locked = False
>     ActiveSheet.Protect
>     Sheets("Recording Sheet").Range("G28:I28").Select
>     ElseIf Range("G26").Value = "" Then
>     ActiveSheet.Unprotect
>     Sheets("Recording Sheet").Range("E28") = ""
>     Sheets("Recording Sheet").Range("G28:I28") = ""
>     Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
>     Sheets("Recording Sheet").Range("G28:I28").Locked = True
>     ActiveSheet.Protect
>     Else
>     ActiveSheet.Unprotect
>     Sheets("Recording Sheet").Range("E28") = ""
>     Sheets("Recording Sheet").Range("G28:I28") = ""
>     Sheets("Recording Sheet").Range("G28:I28").Interior.ColorIndex = 1
>     Sheets("Recording Sheet").Range("G28:I28").Locked = True
>     ActiveSheet.Protect
>     End If
>     End If
> End Sub
>
> Any help would be appreciated.
>
> Thanks
> Noemi


0
Don
11/7/2005 1:36:12 PM
Reply:

Similar Artilces:

Sorting by Color in Excel 2000
I want to sort text by color. I have a worksheet with some rows in black font and some in red. I need to group the rows in red font together. Is there anyway to sort by color? Thanks Hi see: http://www.cpearson.com/excel/SortByColor.htm not possible without VBA -- Regards Frank Kabel Frankfurt, Germany "Greg" <gregor1974@hotmail.com> schrieb im Newsbeitrag news:0ce901c4ac89$2be98dc0$a301280a@phx.gbl... > I want to sort text by color. I have a worksheet with > some rows in black font and some in red. I need to group > the rows in red font together. Is there anyw...

Incorrect formatting opening .csv file in Excel
When opening a database generated .csv file with the following data: "1","10E-024","1" Excel opens it incorrectly formatting the text as a scientific number: 1 1.0E-24 1 If I save the file as .txt, and open in excel using the import wizard, I can specify that the column should be text, and the item code 10E-024 appears correctly. However this is far from ideal, as it relies on a manual step and the file cannot be saved as .csv again - otherwise the same fault happens next time the file it opened. Is there a way of forcing Excel to open .csv files in raw da...

Installing and using Office: MAC 2008 Home and Student Addition
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) My husband recently purchased a new MacBook Pro. He gave me his MacBook Air. Office Home and Student addition was installed on his old computer (MacBook Air), and was loaded on the new computer. I purchased my own copy of Office: MAC 2008 Home and Student and installed it on the MacBook Air but can't use when my husband is using his version. The product keys are obviously different, but when he's writing in Word, I cannot. The message is that two people can not use the program at once who are on the same network. What do I do so ...

Excel #21
I have another question for this paper that I'm working on and I can't seem to find the answer to. I'm a new user for Excel and I know next to nothing about it. Any help you can give me would be great... The question is: "If you see a spreadsheet with row AND column headings as NUMBERS, what is the problem (if any!!)? Tools > Options > General tab > uncheck R1C1 reference style > OK "allybell1980" wrote: > I have another question for this paper that I'm working on and I can't seem > to find the answer to. I'm a new user for Excel...

Merging Excel Sheet with Excel Sheet (Just like in MS Word!)
Hi everyone, Purpose: create classroom roster between one Excel spreadsheet as data source and another excel sheet. I know how to use one excel spreadsheet as a data source to merge with another word document; however, by using this method, I found that the link between the files keep dropping from time to time, especially when the folder has been moved around. I'm thinking of creating the same layout of the Word document in another Excel spreadsheet and somehow "LINK" the data source and the other spreadsheet together, but I'm not sure how, or whether thi...

Push single contact field data into prepared excel workbook
I am new to macros but ameager to get some formulated to help run my small business. I have been trying to find some code which suits my requirement of pushing signgular fields from a contact into a pre-prepared excel book which is loaded with the next stage of macro-powered automation. I recieve enquiries both via email and by phone. I use my blackberry to save phone enquiries to address book and Anagram for Blackberry to capture the required contact data from our email enquiry form. Both are then synchronised with my Outlook contacts. Either from the point of creation, or through ...

Read from Barcode Reader in Excel and Auto hit enter
I am trying to read a barcode off an item, it then goes into it's place in the excel sheet but I cannot get it to automatically hit enter, I have to walk back to machine to hit it. As I recall (12 years ago...) there is a setting on the barcode reader to add Line Feed -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Neo" <Neo@discussions.microsoft.com> wrote in message news:FB95BB3A-91C8-4965-9528-7D32920B81EB@microsoft.com... >I am trying to read a barcode off an item, it then goes into it's place in > the excel sheet but I cannot ...

Excell 2002 xp
need to enable file sharing so that we can have version control on a Excel document Tools, Share Workbook, select (put a check mark into) the option "Allow changes by more than one user...." Then make choices in the Advanced tab as desired.... MRO "sarosh Tafti" <sarosh.tafti@mcdanalds.ca> wrote in message news:071401c38219$9bcc8110$a401280a@phx.gbl... > need to enable file sharing so that we can have version > control on a Excel document ...

How can I have an initial zero in an Excel cell?
I want to be able to enter times as eg 0845 but no matter what cell format I use, there is a problem, either that the initial zero is lopped off or that I am informed there is an error. Is there any way of solving this? try <format><cell><number><custom> enter 0000 and you will get a four digit display with an initial display with a leading zero. Note this is not a time function "ALupin" wrote: > I want to be able to enter times as eg 0845 but no matter what cell format I > use, there is a problem, either that the initial zero is lopped off or...

how to find used region with macro
Hi, sorry if this obvious or has been asked recently. I have a macro that goes through all rows and all columns in a selection. If a row or a column is hidden, the font of that row or column is changed. A friend wants to use the macro in a Workbook with many sheets. He asked if I could change it so that he does not need to do a selection on every sheet before he runs it. I imagine that a nested loop through all rows and all columns will take a long time? I�ve tried to figure out how to get the range defined by the last row and the last column filled with data. First I tried with Current...

excel causing system to be in low system resource
Hi all. Whenever i open my excel application, i am always prompted low system resource and have to exit the application. However all my other office applications like word, access work fine when i open them. I face similar problems before and i reinstall excel to solve the problem. Is there a better solution which can solve this problem once and for all? Thks in advance ...

Change theme colors in Excel
Working in the Excel 2010 Beta version, but I think it's the same in 2007... How can I change the theme and/or standard colors? I can find the colors I want with "more colors" but I have to add to recent colors EACH time I use Excel. Any way to change default settings for the color palette? ...

Copy sheet in Excel
Hi Wonder if anyone of you encounter this problem before? When I try to copy worksheet in a workbook, the following prompt comes up, indicating name conflict. "A formula or sheet you want to move or copy contains the name 'aaaa', which already exists on the destination worksheet. Do you want to use this version of the name? - To use the name as defined in the destination sheet, click Yes - To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box" When I clicked yes, another prompt comes up indicating ...

Need help with using a bar graph in a report
I have a report that separates information about representatives by manager. I need the graphs to give a quick summary on a few stats from each rep but only show for that manager's team and not all information. For example: Manager A has RepA, RepB, and RepC under him. Each rep has Aux1, Aux2, Aux3 information which needs to be displayed. My goal is to show Manager A with his Reps A,B, and C of their Aux1, 2, and 3. Then, under the next manager Header I want Manager B to show Rep D, E, and F with their Aux1, 2, and 3 information. Etc thru Manager G. Please help... Th...

Parameter Query using date ranges
I am creating a parameter query where I want to pull data between 2 dates. In the date criteria of my query, if I write between #01/01/2008# and #01/31/2008# +1, I will get all data for January. If I use a form to enter the dates and have Between [Forms]![autoexec]![start] And [Forms]![autoexec]![end] +1 in the date criteria, I get an error. Does anyone know what I should put in the criteria to make the query work? Are you entering your dates as m/d/y? What happens if you try: DateAdd("d", 1, [Forms]![autoexec]![end]) Is the form open? -- Duane Hookom Microsoft Access...

Can't open database window using shift key...
Hi there, I have an Access database that I distribute to my client on CDROM. The start up options of the database are set so that the database window is not visible to the users. When the database is updgraded the database manager copies it from the CDROM to the relavant PC and then uses the shift key on opening in order to display the database window and link to the tables in the existing back-end. This procedure has been done many many times without a problem... BUT last week he found that holding down the shift key whilst opening the database no longer opened the database window and leave...

How do I setup a name, home address and phone to automatically at.
when I used Outlook Express I had setup a name, home address and phone number that automatically appeared on my outgoing email. How do I setup the same for "Outlook" email which I recently purchased with Office 2003 Professional? Set it up in your signature, it's in the tools/options of Outlook. -- Kathleen Orland Outlook Tips: http://www.outlook-tips.net/ http://www.howto-outlook.com/ Outlook & Exchange Solutions Center: http://www.slipstick.com "Robert C. Sollars" wrote: > when I used Outlook Express I had setup a name, home address and phone numb...

[Excel 2003] problem in files with pivottables after install Office Service Pack 3
Hi all. System: Windows XP Pro SP2, Office 2003 Professional + Service Pack 3 I have a problem with some xls files after install office service pack 3. With service pack 2 this files normal open. With service pack 3 after open file displays dialog (my translate from russian) "In workbook ... have contents which can not be read. Try restore contents of workbook ? If you trust source of this workbook, press button YES". If I press "No" - file not opens. If I press "Yes", displays next dialog. This dialog form content a list of corrections in file. Biggest pa...

Suggestion for using CStringArray with Unicode, mulit-byte, ATL &
Hi, I couldn't find the "development" group so i chose this one. I am trying to bring some code that has the CStringArray in it from a Multi-Byte project into a Unicode project and I keep getting errors I cannot resolve. Scenario: Project type: DLL Use MCF: Shared DLL Use ATL: Not using ATL Min CRT Use in ATL: No Char set: Unicode added afxcoll.h to stdafx.h as suggested elsewhere #include <afxcoll.h> #include "resource.h" #include <atlbase.h> #include <atlcom.h> I get the following error; Error 247 error LNK2005: _DllMain@12 already defined in Te...

Using Access 2003 for Inventory
I plan to setup our inventory system in the warehouse using Access 2003. Now, what I would like it to do (and it has been a long time since I have tried to do anything with access so I don't remember if this is possible or not) is automatically create a report that will tell me when I am low on equipment and possibly e-mail it to me and my supervisor. I'd also like to do the samething so with our drop material (cables, ground blocks, etc.) and have it e-mail to the owner so that he can place an order. The system, ideally, will also allow me to view what our technicians have on ...

Combining Excel files
This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C39406.568A3A80 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have all my timesheets in separate files and I would like to extract = values from each file to use in analyzing my time spent on projects and = time spend doing administrative tasks, etc. Can someone give me some tips on how to get started (e.g.. how to = extract valves from one file to another) Thanks Cory ------=_NextPart_000_0012_01C39406.568A3A80 Content-Type: text/html; charset="iso-8859...

how to fix cells in excel and other move
I saw alot of excel wher some celles do not move and other move up and down so can any body help me to know how to make it Try this. Click on a cell, say B2 and pull-down: Window > Freeze Panes -- Gary''s Student - gsnu200847 "Bassam" wrote: > I saw alot of excel wher some celles do not move and other move up and down > so can any body help me to know how to make it ...

How to put VBA formula in Excel Sheet
hi there, how can i fix the error for this VBA? Sub PutDate() Range("A1") = "="Date :" & TEXT(TODAY(),"dd-mm-yy")" End Sub pls. help me for this. thanks in advance. EricBB One way: Public Sub PutDate() Range("A1").Formula = "=""Date :"" & TEXT(TODAY(),""dd-mm-yy"")" End Sub Note that quotes within the formula string must be doubled. In article <AA203817-803E-40C2-B617-7B1BDC3E1A59@microsoft.com>, EricBB <EricBB@discussions.microsoft.com> wrote: &...

Linking Project To Outlook Global Address Book
Good day everyone, hope everyone had an enjoyable Thanksgiving day. Appreciate it if you could tell me how to solve the following issue. I am trying to add names in my resources so that it will match the names as listed in Outlook (i.e. names separated with a comma). However I keep getting the the following error message: "Invalid name for a resource. Resource names cannot contain brackets ([]) or the list separator charactor." Is there a way to resolve this. The program that I am using uses the names listed in the Microsoft Global address book. Is there a way to li...

How can I use Outlook in two computers (my PC and my laptop)? I o.
How can I use Outlook in two computers (my PC and my laptop)? I can only see my incoming emails in my PC, but not in my laptop; even though I have configured Outlook in both computersto received the incoming emails to my university account. "osiris" <osiris@discussions.microsoft.com> wrote in message news:9C5ACFC5-3859-4FB0-BC20-3AC7A9B5F6A3@microsoft.com... > How can I use Outlook in two computers (my PC and my laptop)? I can only > see > my incoming emails in my PC, but not in my laptop; even though I have > configured Outlook in both computersto received th...