Saving Cell Address by selecting It

Re: Saving Cell Address by selecting It

Try placing this code in the module of the sheet that will be used to select the target cell:

Dim SelectOn As Boolean
Dim myCell As String


Sub getCell()


SelectOn = True
MsgBox "Please select cell"


End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If SelectOn = True Then
myCell = Target.Address
Sheets("Sheet1").Range("a1") = myCell
SelectOn = False
End If
End Sub

For good measure I prefer to set any variable such as this to False when the workbook opens to be sure there are no problems. In the ThisWorkbook module:

Private Sub Workbook_Open()
SelectOn = False
End Sub

Regards
Weasel

Re: Saving Cell Address by selecting It

use this

Sheets("Data").Range("a1") = Application.InputBox("Please select a cell", Type:=8).Address

Re: Saving Cell Address by selecting It

Hmmm - nice Rich! Like it...

But AbuYehya, that isnt what you asked for the first time at all... Please try to specify as accurately as you can, what it is you need or time may be wasted generating useless solutions.

What Rich has cunningly suggested will work with a small addition:

Dim thisWKBK As String
thisWKBK = ActiveWorkbook.Name
Workbooks(thisWKBK).Sheets("Sheet1").Range("a1") = Application.InputBox("Please select a cell", Type:=8).Address(External:=True)

Re: Saving Cell Address by selecting It

Worked for me - maybe I just got lucky....

I ran the macro from the book that contained the code. When the input box popped up I selected Window - and the file I wanted, then clicked on a cell.

What are you doing when the code is not acheiving the desired result?

Re: Saving Cell Address by selecting It

Intersting that it doesnt work that way!

I would not recommend rolling this out to other users with such a fine window of success. Will try to look at this some more to make it a little more bullet proof.

Regards
Weasel

Re: Saving Cell Address by selecting It

OK - as far as i can tell....

By changing xl files via the taskbar you are leaving one instance of the application and entering another - the macro doesnt like this.

You can prevent this from being an option by:

With Application
.ShowWindowsInTaskbar = False
End With

BUT (big BUT, like mine) it's rarely a good thing to mess about with a users interface. if you do, be sure to restore the users original setting.

Regards
Weasel