Remember Last Cell
 

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

Remember Last Cell
 

On 18 Feb, 12:38, "ExcelSwede" wrote:
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

Remember Last Cell
 

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



"ExcelSwede"

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

Remember Last Cell
 

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" wrote:

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

Remember Last Cell
 

Brilliant solution! Many thanks!

/Chris

"Jim Cone" wrote in message
...

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



"ExcelSwede"

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