[RESOLVED] [Excel] Range Select Dialog

The problem: To get the user to select the row that contains the data required in another workbook that is open.

I wanted to create something similar as the standard xldialog for selecting a range. (for example when specifying the source data for a chart)

I tried:

Code:

---

Application.Dialogs(xlDialogChartAddData).Show

---

but get a "Show method dialog class failed" error.

Then I made my own form that appears similar. My probem in getting the form to function correct is to set the textbox equal to the active cell selected:

Code:

---

Me.txtTarget = ActiveCell.Value

---

The code works if I call it from a command button event, but I don't know how to get it to update everytime the user clicks a new cell in the other workbook.

My code is in a macro as it will function as an add-in, called from a toolbar button.

If it wasn't a foreign workbook, I could have used the following event:

Code:

---

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
frmTarget.txtTarget = ActiveCell.Value
End Sub

---

I am using VBscript.

Any suggestions?

Hi again,

you can still use the worksheet_selectionChange event with just a little change.

You can call macros/sub inside an Add-In - example >

vb Code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
Application.Run "'Add1.xlam'!test"
 
End Sub

Where 'Add1' = the name of your Add-In
& 'test' = the Macro name

All you need then is a macro inside your add-in that calls your form also inside you Add-In !!!!

Make sure you put the Macro inside a Module !!!

Also you can pass arguments to your macro like this -

vb Code:

Application.Run "'Add1.xlam'!test", "hello"

just add a comma and then the argument for each argument you declare in your macro

Example - test macro

vb Code:

Public Sub test(strMessage As String)
 
MsgBox (strMessage)
 
End Sub

Very happy to see you are online!

The worksheet of which the selection will change is not my own. It is from an external party which the user will be able to open through my add-in (Thanks to your help :) )

I can therefore not put any code inside his worksheet under the selection change event. How do I overcome this? Or am I missing something in your reply above?

Thanks

It's exactly what I was looking. I new it had to be simple to cost me 10 hours to try and figure it out!

I now have a bug though? The refedit does not release control. Once clicked and a cell selected, the focus stays on the active workbook and does not allow the user to click the userform. My userform is set as vbmodeless.

in an addin you can declare the application with events, in a class module, you can then get events from any other workbooks once opened, with out code in there own events

right lets start again then

put the following code under your ThisWorkbook object in your Add-In

vb Code:

Private WithEvents App As Application
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
 
MsgBox "test"
 
End Sub
Private Sub Workbook_Open()
    Set App = Application
End Sub

Save it, close Excel and open again !!!

when you select a cell it should fire the - App_SheetSelectionChange sub !

Snap

I get the message - thanks it works.

1. Only thing - how do I stop it from working? At the moment I simply hide the form where the active cell adress is displayed each time there is a sheetselectionchange event, but it is still running in the background.

2. Any idea how to regain control after calling and selecting a cell with the refedit control?

For other people troubleshooting this in future:

I figured out the answer to my second question regarding "refedit" control. If I show the userform as modal and not vbmodeless the problem goes away.

Part 1 of my post above the guru's will have to answer.

right under what circumstances do you want it not to work ?????

As you rightly said the selection Change event will fire every time, if you have some criteria where you do not want the control to launch then you could put an if / case statement in or something.

Not sure that you can disable the event firing itself though.

if sh.name = "somesheet" then
or if target.address = "w73" then

you can also use target.row or column or any other property of sh (sheet) or target (range)
or you could use sheet change event rather that sheet selection change