Results 1 to 6 of 6
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Controlling UserForm (97:SR2)

    I'm having an issue controlling Userform1 that exists in Workbook01 from Workbook02.

    Example Code in WB02: Userform1.Checkbox1 = True "Userform1 exists in WB01"
    I have tried: Workbooks("WB01").Userform1.Checkbox1 = True but get an error message that the object doesn't support this property or method.

    Any suggestions or references would be appreciated.

    Thanks,
    John

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Controlling UserForm (97:SR2)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> John

    I need to ask a few questions before I can give you a half baked answer:

    Are the two forms displayed at the same time?
    Can you use the Me.Checkbox1 = True vs the name of the UserForm?
    Can you rename the forms so that they have unique names?

    I was under the impression that you can't have two UFs displayed at the same time, and thus one will be the active one, and that means Me.something will refer to the object on that form, which is the active one.

    If you have a hidden form, Me.Hide, I still tend to think that Me.something will refer to the active form, if the code is not running from the hidden form <img src=/S/confused.gif border=0 alt=confused width=15 height=20>?!

    So in the end try Me.something and see if it works.

    Hope this is coherent... <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling UserForm (97:SR2)

    Wassim,

    There is only one userform which is in workbook01. I have code in WB02 which I want to control the userform in workbook01.

    This should be quite simple to code but I painted myself into a corner.

    Thanks,
    John

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Controlling UserForm (97:SR2)

    I think that the only ways to initialize a control in a user form is to:
    1) manually set the property
    2) set the code in the initialization/activation event either:
    a) hard code: .checkbox1 = true
    [img]/forums/images/smilies/cool.gif[/img] via a public variable: .checkbox1 = bVariable
    c) via a cell reference: .checkbox1 = : Workbooks("WB02").sheets("sheet1").range("A1").val ue

    I do not think you can change the values in code from another form. You would have to store the value somewhere and have the code for the other userform grab it. SInce you are going across workbooks, I am not sure how well a variable would work, so you could store the value in a range in one of the workbooks and have the other userform get it when it initializes or activates.

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Controlling UserForm (97:SR2)

    It can be done, but it's tedious. The procedures to be run must be in the workbook containing the UserForm, but you can call them from another workbook.

    Put a procedure in a standard module in WB01 that sets the check box:

    Sub SetCheck(blnValue As Boolean)
    UserForm1.CheckBox1 = blnValue
    End Sub

    and call this procedure from WB02 using Application.Run:

    Sub CallOther
    Application.Run "WB01!SetCheck", True
    End Sub

    Note that the argument to SetCheck is passed as a separate argument to Application.Run, without parentheses.

    If the workbook name contains spaces, you must enclose it in single quotes.

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Controlling UserForm (97:SR2)

    OK John

    Some more gibrish:

    1) Why must the UserForm be controlled from another workbook?

    2) Can't you just simply have the UserForm in one workbook, where the code is running from, and have the data that it gets from the user go to the other workbook?
    You can do something like: Userform.checkbox1.value = workbooks("MyWorkbook").sheets("MySheet").range("A 1").value, and the opposite can also be true.

    3) Can you have all the controls' initialization code in the initialization event of the UserForm in Book1 vs the some subroutine code in Book2? What I say is simply have the .Show command in Book2, but all initialization is in the form initialization event and that will fire right after the .Show command.

    I know I am not in front of your computer, and I know some work has been done, but I offer suggestions as I read and understand the situation.

    HTH.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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