+ Reply to Thread
Results 1 to 2 of 2

How to: User Form to assign a user defined range to a macro variab

  1. #1
    TrevTrav
    Guest

    How to: User Form to assign a user defined range to a macro variab

    I want to be able to select a range of cells with a user form (using the
    RefEdit control) and then pass or assign the user defined range address to a
    range varaible in a seperate VB macro. I want to be able to hold the user
    defined range in a range variable so I can reference multiple times within
    the/a "formatting macro".

    I have figured out how to display the form and get the range address
    assigned inside the VB code for the user (CommandButton1 click Sub) but I can
    not figure out how to assign or pass the range value out of the user form to
    the range variable in the "formatting macro". I think if I put all the
    "formatting macro" code in the Sub CommandButtion1_click() it would work or
    this specific application or task.

    Ideally I would like the UserForm to be "generic" of sorts, that way I can
    call it from inside any macro I have (current or future) that performs
    formating on each cell in the range or copying/moving/tranposing etc. the
    range as a whole.

    I think the issue I am having is a miss match of varaible types or not
    setting the correct parameters when referencing variables with repsect to
    their "scope". It also could be something simple that I am just over looking
    - VB is a little new to me. Any assistance or light that could be shed on
    this "hair wringer" is much appreciated. Thanks in advance.


  2. #2
    Tom Ogilvy
    Guest

    Re: How to: User Form to assign a user defined range to a macro variab

    Rather than unload the userform, hide it where you would unload it.

    then in the other code you can so

    sAdd = Userform1.Regedit1.Value
    set rng = Activesheet.Range(sAdd)
    unload userform1

    ---------------------
    General Module:
    Sub Showform()
    Dim sAdd As String
    Dim rng As Range

    UserForm1.Show
    sAdd = UserForm1.RefEdit1.Value
    Set rng = ActiveSheet.Range(sAdd)
    Unload UserForm1
    rng.Select
    End Sub

    ---------------------
    Userform Module:
    Private Sub CommandButton1_Click()
    Me.Hide
    End Sub

    As an example (worked for me)

    --
    Regards,
    Tom Ogilvy

    "TrevTrav" <[email protected]> wrote in message
    news:[email protected]...
    > I want to be able to select a range of cells with a user form (using the
    > RefEdit control) and then pass or assign the user defined range address to

    a
    > range varaible in a seperate VB macro. I want to be able to hold the user
    > defined range in a range variable so I can reference multiple times within
    > the/a "formatting macro".
    >
    > I have figured out how to display the form and get the range address
    > assigned inside the VB code for the user (CommandButton1 click Sub) but I

    can
    > not figure out how to assign or pass the range value out of the user form

    to
    > the range variable in the "formatting macro". I think if I put all the
    > "formatting macro" code in the Sub CommandButtion1_click() it would work

    or
    > this specific application or task.
    >
    > Ideally I would like the UserForm to be "generic" of sorts, that way I can
    > call it from inside any macro I have (current or future) that performs
    > formating on each cell in the range or copying/moving/tranposing etc. the
    > range as a whole.
    >
    > I think the issue I am having is a miss match of varaible types or not
    > setting the correct parameters when referencing variables with repsect to
    > their "scope". It also could be something simple that I am just over

    looking
    > - VB is a little new to me. Any assistance or light that could be shed on
    > this "hair wringer" is much appreciated. Thanks in advance.
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1