Results 1 to 12 of 12

Thread: [RESOLVED] [Excel] Range Select Dialog

  1. #1

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    Resolved [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?

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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:
    1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    2.  
    3. Application.Run "'Add1.xlam'!test"
    4.  
    5. 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



  3. #3
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [Excel] Range Select Dialog

    Also you can pass arguments to your macro like this -

    vb Code:
    1. 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:
    1. Public Sub test(strMessage As String)
    2.  
    3. MsgBox (strMessage)
    4.  
    5. End Sub
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  4. #4

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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

  5. #5

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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.

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [Excel] Range Select Dialog

    right lets start again then

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

    vb Code:
    1. Private WithEvents App As Application
    2. Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    3.  
    4. MsgBox "test"
    5.  
    6. End Sub
    7. Private Sub Workbook_Open()
    8.     Set App = Application
    9. 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



  8. #8
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: [Excel] Range Select Dialog

    Snap
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  9. #9

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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?

  10. #10

    Thread Starter
    Member
    Join Date
    Oct 2008
    Location
    South Africa
    Posts
    32

    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.

  11. #11
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    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



  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width