Remember Last Cell

[ExcelSwede]

Hi,

I need to remember the last destination selected in an inputbox as the
default input until next time a macro is run.

I started out with declaring a public variable to keep the address

Public LastDestination As Object

Then I used this variable as default input in the list box

If LastDestination Is Nothing Then
Set DestinationCell = Application.InputBox( _
prompt:="Select Destination", Type:=8)
Else
Set DestinationCell = Application.InputBox( _
prompt:="Select Destination", Type:=8,
Default:=LastDestination.Address)

End If

Set LastDestination = DestinationCell

Challenge: I only get a "local" address, e.g. "B5", and not the full address
with filename and sheetname included, e.g. [Book1]Sheet1!$C$5.

Any hints?

ExcelSwede

 

[WhytheQ]

Hi,

I need to remember the last destination selected in an inputbox as the
default input until next time a macro is run.

I started out with declaring a public variable to keep the address

    Public LastDestination As Object

Then I used this variable as default input in the list box

    If LastDestination Is Nothing Then
        Set DestinationCell = Application.InputBox( _
            prompt:="Select Destination", Type:=8)
    Else
        Set DestinationCell = Application.InputBox( _
            prompt:="Select Destination", Type:=8,
Default:=LastDestination.Address)

    End If

    Set LastDestination = DestinationCell

Challenge: I only get a "local" address, e.g. "B5", and not the full address
with filename and sheetname included, e.g. [Book1]Sheet1!$C$5.

Any hints?

ExcelSwede

can you just change the last line of code to the following?
Set LastDestination = "[" & thisworkbook.name & "]" & activesheet.name
& "!" & DestinationCell

 

[Jim Cone]

MsgBox LastDestination.Address(external:=True)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"ExcelSwede"
<[email protected]>
wrote in message
Hi,
I need to remember the last destination selected in an inputbox as the
default input until next time a macro is run.
I started out with declaring a public variable to keep the address
Public LastDestination As Object
Then I used this variable as default input in the list box

If LastDestination Is Nothing Then
Set DestinationCell = Application.InputBox( _
prompt:="Select Destination", Type:=8)
Else
Set DestinationCell = Application.InputBox( _
prompt:="Select Destination", Type:=8, Default:=LastDestination.Address)
End If

Set LastDestination = DestinationCell

Challenge: I only get a "local" address, e.g. "B5", and not the full address
with filename and sheetname included, e.g. [Book1]Sheet1!$C$5.
Any hints?
ExcelSwede

 

[FSt1]

hi
here is something for you to think about. it drops the active file name,
active sheet name and input box input into cell a1. not what you want but
maybe it will give you ideas.
Dim fn As String
Dim sh As String
Dim ca As String
fn = ActiveWorkbook.Name
sh = ActiveSheet.Name
ca = InputBox("select destination")
Range("A1").Value = fn & ":" & sh & ":" & ca

regards
FSt1

 

[ExcelSwede]

Brilliant solution! Many thanks!

/Chris