Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL Odd behavior interaction between dynamic range andSheet_Deactivate macro
I keep a daily log of activities in and Excel Workbook. Each activity
worksheet is one week and is named for Monday's date, as in "Dec 23". In col A I enter the date in 26-Dec format. In Col B I put the activity. Col C does a vertical lookup based on Col B and a table in another worksheet called "Sheet2". Sheet2 contains only a single dynamic range called "Namelist" and the name is defined as =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),2) It contains a list of the regular activities I do. The VLOOKUP takes the activity description and looks up who I do that for and places their name in Col C. The "Sheet2" activity list changes from time to time. When I pick up a new regular activity, I add it to the "Sheet2" list so the VLOOKUP can put in the correct name on the weekly sheet. Sometimes when I no longer do an activity, I delete it from the "Sheet2" list. This is where the problem is occuring now. There is also a sheet macro associated with "Sheet2". It automatically resorts the activity list on Col A, the list of activities, when I deactivate the sheet. This way, I don't have to resort the list after I add or delete activities, the macro does it for me. But I'm getting some very strange behavior since I added the macro. Here is the macro: Private Sub worksheet_deactivate() 'Creates dynamic range in worksheet module in DGBStatus workbook 'and sorts dynamic range on Col A for vlookup in other sheets Dim rNamelist As Range ActiveWorkbook.Names.Add Name:="namelist", RefersToR1C1:= _ "=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1),2)" Set rNamelist = Sheet15.Range("namelist") rNamelist.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom End Sub (Sheet15 is the absolute for "Sheet2") When I first added the macro, it didn't have the first line inserting the dynamic range formula into the spreadsheet, and when I deleted lines from the range, the macro would hang and I'd get a range variable not working message. I found that the formula for the "Namelist" range was getting screwed up somehow when I deleted some lines from the range and it returned a "method range of object worksheet failed" error on the "Set rNamelist..." line. So I added the line to insert the correct formula everytime the macro runs. Seemed like a good idea at the time. But now, when I add or delete line(s) from the "Namelist" range, the macro runs but when I click on the activity worksheet. I get very weird results. If I click on the next blank cell in Col B, the cursor appears in that cell, but there's no border to indicate I've selected it. When I click on any other cell in the spreasheet, that cell and the previously selected cell both are higlighted, almost as though I control clicked on the secone one, except that the originally selected cell is NOT in reverse, like a previously selected cell in a Control click sequence normally would be. As I click on different cells at random in the activity sheet, the currently selected cell and the originally selected call remain with the heavy borders around both of them! This behavior continues until I activate another application or close and reopen the spreadsheet, at which point it stops until I add or delete another line from the "Sheet2" activity list. Is this a known bug? A strange interaction between a dynamic range and an event driven macro? Any ideas or suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range Name Macro | Excel Discussion (Misc queries) | |||
dynamic range in a macro | Excel Programming | |||
dynamic range based on criteria, within a dynamic range, passed to a function | Excel Programming | |||
creating a dynamic range based on criteria, within a dynamic range, and passing it to a function | Excel Programming | |||
Macro interaction | Excel Discussion (Misc queries) |