D
davegb
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?
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?