Tags for worksheets

[Bob]

Hi Everyone:

In excel 2003, I have an userform. When the user closes the form, it should
save somewhere the address of the active cell on the active sheet. Then,
when ever the form opens, I want it to recall the active cell on the current
active sheet that it saved before. Of course this address may be different
for different sheets. The ideal place would be in the tag of the active
sheet. I tried a few things but it did not work. Any ideas what is the
best way to achieve saving he address. Thanks for all your help.

Bob

 

[JP]

Why do you need to save the address of the active cell?

--JP

 

[Peter T]

Sub test()
Dim rLastActive As Range

If GetLastActive(rLastActive) Then
MsgBox rLastActive.Address
Else
MsgBox "LastActive not set"
End If

ActiveCell.Next.Select

SetLastActive

End Sub

Function GetLastActive(cel As Range) As Boolean
Dim nm As Name
On Error Resume Next

Set cel = ActiveSheet.Names("LastActive").RefersToRange
GetLastActive = Not cel Is Nothing

End Function

Function SetLastActive()

ActiveSheet.Names.Add("LastActive", ActiveCell).Visible = False ' or
true

End Function

Regards,
Peter T

 

[Bob]

Thank Peter. The code is great. However, I was wondering do the sheets
have a Tag property (like controls in the regular VB) where I can save some
things to? Thanks.

Bob

 

[Peter T]

No, Worksheets do not have a Tag property.

But I don't understand your apparent reluctance to using Names. Apart from
the possibility to refer to a range, as in the example, a Name can also
store static non-range data.

With worksheet level names you can name names similarly in each sheet.

Regards,
Peter T

 

[Bob]

My reluctance has three reasons (which may be due to my lack of knowledge).
1- What if the user has already defined such a name? This is a minor one,
as I can choose some really strange name, and the probability of the user
choosing the same strange name is virtually zero. Not a big deal.

2- What if the user deletes the row or the cell containing the name? Then
the name is gone, but the last active cell is still valid.

3- Can the names store some data?

You see, on my form I have a listbox that shows a list of the rows that have
data. On the first instance the form is opened, the first item in the list
is selected by default. Then I want the form to remember the last item the
user clicked, so that the next time the form is opened, the same item to be
picked by default. As an example, a row may contain data, let's say 'Row
5'. The user opens the form selects 'Row 5' and clicks OK for the program
to do its thing. Then, deletes 'Rows 5', does more work, adds something to
'Row 5' and opens the form again. Since 'Row 5' is still valid (as it
contains some data), the form should still select 'Row 5' as the default.
However, if the user does not add anything to 'Row 5' after the delete, the
form should select the first item on the list upon opening the form. I know
how to check to see if a row contains data or not. My problem is storing
somewhere the row number.

I know in the original posting, I did not explain my task clearly, but this
is what I am trying to do. I guess, using your method, I can accomplish
this using names. This would resolve problems 1 and 3 above, but problem 2
still remains. What is the user deletes the row containing the name I have
selected?

I would appreciate your thoughts and/or other possible solutions now that
you see what I am trying to do.

Bob

 

[Bob]

I guess, I can use the CustomProperty of a sheet? What do you think?

Bob

 

[Peter T]

My reluctance has three reasons (which may be due to my lack of
knowledge).
1- What if the user has already defined such a name? This is a minor one,
as I can choose some really strange name, and the probability of the user
choosing the same strange name is virtually zero. Not a big deal.

Providing you choose your names carefully assume zero, particularly if you
'hide' the name as in the example.

2- What if the user deletes the row or the cell containing the name? Then
the name is gone, but the last active cell is still valid.

Other way round, the name remains but you'll get a ref error.

If the activecell is deleted (row/column deleted) it really does no longer
exist. The fact that the name exists with the ref error lets you know what
the user has done in the meantime. Alternatively just store the cell's
address (not a range). Though of course that might no longer refer to the
actual cell if rows/col's have been inserted/deleted in the mean time.

3- Can the names store some data?

Sure, as I mentioned earlier

ActiveSheet.Names.Add "myData_01", 123
' parse out the =, max lenth is 255
MsgBox Mid$(ActiveSheet.Names("myData_01"), 2, 255)

You see, on my form I have a listbox that shows a list of the rows that
have data. On the first instance the form is opened, the first item in
the list is selected by default. Then I want the form to remember the
last item the user clicked, so that the next time the form is opened, the
same item to be picked by default. As an example, a row may contain data,
let's say 'Row 5'. The user opens the form selects 'Row 5' and clicks OK
for the program to do its thing. Then, deletes 'Rows 5', does more work,
adds something to 'Row 5' and opens the form again. Since 'Row 5' is
still valid (as it contains some data), the form should still select 'Row
5' as the default. However, if the user does not add anything to 'Row 5'
after the delete, the form should select the first item on the list upon
opening the form. I know how to check to see if a row contains data or
not. My problem is storing somewhere the row number.

I know in the original posting, I did not explain my task clearly, but
this is what I am trying to do. I guess, using your method, I can
accomplish this using names. This would resolve problems 1 and 3 above,
but problem 2 still remains. What is the user deletes the row containing
the name I have selected?

Reading the above looks like you need to store the row number rather than a
range (example given above)

Regards,
Peter T

 

[Peter T]

I guess, I can use the CustomProperty of a sheet? What do you think?

Must admit I didn't think of that, good idea though I don't see any strong
advantage over names. Keep in mind this property is n/a in XL97/2000, IOW no
good in user's unknown version.

Regards,
Peter T

 

[Bob]

Thanks Peter. I will play with your idea.

Bob

Must admit I didn't think of that, good idea though I don't see any strong
advantage over names. Keep in mind this property is n/a in XL97/2000, IOW
no good in user's unknown version.

Regards,
Peter T