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

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

--JP

On Sep 22, 4:24*pm, "Bob" wrote:
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

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" wrote in message
...
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

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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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


"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

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

Bob

"Peter T" <peter_t@discussions wrote in message
...
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" wrote in message
...
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" <peter_t@discussions wrote in message
...
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" wrote in message
...
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

"Bob" wrote in message
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

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

Thanks Peter. I will play with your idea.

Bob

"Peter T" <peter_t@discussions wrote in message
...
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