Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Syntax constructing references to forms (VBA/Word 2003)

    Dear Loungers,
    I have tried in vain to solve this for myself. My code logically looks like this:

    1 sub Module 1
    2 Call UpdateRoutine
    3 end sub
    4
    5 sub UpdateRoutine
    6 For each control in frmFileProperties.controls
    7 do stuff
    8 Next
    9 end sub

    I am trying to genericise the update routine so need to pass the form name. My problem is I can

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Syntax constructing references to forms (VBA/Word 2003)

    Something like this:

    Code behind a commandbutton on the form:
    <pre>Private Sub CommandButton1_Click()
    iteratecontrols Me
    End Sub
    </pre>


    then in a normal module:
    <pre>Sub iteratecontrols(frm As UserForm)
    Dim ctl As Control
    For Each ctl In frm.Controls
    MsgBox ctl.Name
    Next ctl
    End Sub
    </pre>


    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Syntax constructing references to forms (VBA/W

    Lovely!

  4. #4
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Syntax constructing references to forms (VBA/W

    Rory,

    Works really well, but related to this is use of Hide. I wanted to do something like this:

    In my Form code:

    1. Private Sub CB_Cancel_Click()
    2. Call HK.UpdateProperties.FormCancelClick(Me)
    3. End Sub

    then in HK.UpdateProperties:

    4. Sub FormCancelClick(frmCurrentForm As UserForm)
    5. frmCurrentForm.Hide ' hide the form
    6. End Sub

    When it runs FormCancelClick an error is reported and line 5 is indicated. The error is 438 "doesn't support property or method". The code used to sit in the Form code and read "Me.Hide" which worked perfectly. Obviously not in an external module Hide doesn't want to be applied in this way. Is it my syntax or can't I do this?

    thank you................... liz

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

    Re: Syntax constructing references to forms (VBA/W

    This appears to be a bug in the object model. If you use frmCurrentForm As UserForm, a lot of the methods of a userform don't work. If you use frmCurrentForm As Object, you won't be able to use IntelliSense, but methods such as Hide will work. So try

    Sub FormCancelClick(frmCurrentForm As Object)
    frmCurrentForm.Hide ' hide the form
    End Sub

  6. #6
    4 Star Lounger
    Join Date
    Nov 2002
    Location
    London, Gtr London, England
    Posts
    496
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Syntax constructing references to forms (VBA/W

    Hans,

    Thank you ... I wonder if I'll ever get all the exceptions?

    liz

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

    Re: Syntax constructing references to forms (VBA/W

    Probably not - this one was new to me too <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    We all learn by trial and error...

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Syntax constructing references to forms (VBA/W

    Userforms are probably the strangest things in the entire Excel Object Model, in my opinion. They are sort of a class, sort of an object and not really either. I think somebody at MS was on something when they decided to create a class that was also an instance of itself. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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