Get address from external file by onclick if possible
Here is my dylema:
I want to build a form which uses another file to do certain changes. In this form the user has to fill-in the exact address of a certain cell from the external file.
What I want is: Instead of the user typing: cell "A2" from sheet "alpha" from workbook "wk1", The user to browse for the file (already done), and just by clicking that cell, to fill the form with that cell's address.
basically:
1. open file
2. add an onclick event of some kind.
3. confirmation textbox "Are you sure this is the correct address?" (loop until "yes")
4. grab the address, close the file (w/o saving) and fill in the form with appropriate address.
Here's one inelegant solution
Here's one inelegant solution :-
Add a code module to each of the "slave" workbooks and paste the following code :-
Option Explicit
Global IsOpen As Boolean
Sub TestForMainWb()
On Error Resume Next
If IsError(Windows("Main.xlsm").Activate) Then
IsOpen = False
Else
IsOpen = True
End If
On Error GoTo 0
End Sub
Add the following code to each of the slave ThisWorkbook modules:-
Private Sub Workbook_Open()
TestForMainWb
End Sub
Add the following code to each sheet of each slave workbook :-
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iResponse As Integer
If IsOpen = False Then Exit Sub
iResponse = MsgBox(Target.Address, vbYesNo, "Confirm Selection")
If iResponse = vbNo Then Exit Sub
Windows("Main.xlsm").Activate
ActiveSheet.Range("A1").Value = Target.Address
IsOpen = False
End Sub
Main.xlsm needs to be amended to the name of your main workbook.
The target address is pasted into cell A1 of your main workbook - you need to amend the code to suit.
that could work
I did (kind of) the same thing, only a little bit more complex, as my needs required so.
The slave workbook could be any random workbook, so I had to write a procedure to add that code on the fly (programatically). The rest is kind of the same idea. Thanks so much...heavy programming...
Sounds like it .. :-)
Sounds like it .. :-)