-
Jan 8th, 2009, 03:00 AM
#1
Thread Starter
Member
[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?
-
Jan 8th, 2009, 05:34 AM
#2
Re: [Excel] Range Select Dialog
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 !!!
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 8th, 2009, 05:38 AM
#3
Re: [Excel] Range Select Dialog
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
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 8th, 2009, 05:48 AM
#4
Thread Starter
Member
Re: [Excel] Range Select Dialog
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
-
Jan 8th, 2009, 06:20 AM
#5
Thread Starter
Member
Re: [Excel] Range Select Dialog
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.
-
Jan 8th, 2009, 06:21 AM
#6
Re: [Excel] Range Select Dialog
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 8th, 2009, 06:35 AM
#7
Re: [Excel] Range Select Dialog
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 !
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 8th, 2009, 06:36 AM
#8
Re: [Excel] Range Select Dialog
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 8th, 2009, 08:23 AM
#9
Thread Starter
Member
Re: [Excel] Range Select Dialog
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?
-
Jan 8th, 2009, 08:45 AM
#10
Thread Starter
Member
Re: [Excel] Range Select Dialog
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.
-
Jan 8th, 2009, 10:35 AM
#11
Re: [Excel] Range Select Dialog
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.
Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you
-
Jan 8th, 2009, 03:39 PM
#12
Re: [Excel] Range Select Dialog
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|