Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Form vs Form (2000+)

    Hi All,

    I think this is easy.

    Is there any relationship between the form one can easily create from the Data menu (Data | Form) and a form one could create in VBA? I think the answer is no and I certainly don't see anything. Thought I'd ask.

    Another issue: can one take the form from Data | Form and do anything with it with VBA or save it or... I would think one could write a worksheet change event that checks a column that the form is being used for data entry. However, I tried creating a trivial worksheet change event and nothing happened. My worksheet is 2 columns (A and [img]/forums/images/smilies/cool.gif[/img] and I used Data | Form to create a form. As I enter data thru the form, data is being stored in col B. Yet the following macro does nothing:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Range("B:B")) Is Nothing Then
    Exit Sub
    End If
    MsgBox "we're here"
    End Sub

    The above macro is on the sheet module for the same sheet as I'm entering data thru the form.

    thanks

    fred

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form vs Form (2000+)

    There is no relation between Userforms and Data, form AFAIK.

    Indeed the change event doesn't fire, not in XP either.

    WIth VBA you can show the form, but that's all.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Form vs Form (2000+)

    Jan Karel,

    Thanks.

    >WIth VBA you can show the form, but that's all.

    How? I tried to record a macro of clicking the steps associated with Data | Form and got a blank macro.

    BTW: I saw in another thread an answer by you on forms where you talked about programmers more experienced than you (not that I understood your/their answer; Something about setting myForm=UserForm1). But which person on this earth would that be? <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

    Fred

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form vs Form (2000+)

    Activecell.ShowDataForm

    Beware when you're not US though, Dates will be transferred in US format when you show the dataform from VBA.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form vs Form (2000+)

    <hr>I saw in another thread an answer by you on forms where you talked about programmers more experienced than you (not that I understood your/their answer; Something about setting myForm=UserForm1). But which person on this earth would that be?<hr>
    <vbg> Celebrities like Stephen Bullen, Rob Bovey and many others.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Form vs Form (2000+)

    Jan Karel,

    Well there are a lot more experienced people than I.

    >Activecell.ShowDataForm

    doesn't seem to work no matter where I put it (module, worksheet, workbook, even user form module as last resort). Gives an error of object doesn't support this property or method. This is XP.

    Although when I put it in the userform module, it does show the blank UserForm1 form from when I added the form module.

    ???

    Fred

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

    Re: Form vs Form (2000+)

    I think it should be ActiveSheet.ShowDataForm. If you look up ShowDataForm in the online help for Excel VBA, or in the Object Browser, you'll find that it is a method of the Worksheet object.

  8. #8
    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: Form vs Form (2000+)

    In case you are interested, John Walkenbach has a <img src=/S/free.gif border=0 alt=free width=30 height=15> "enhanced Data Form". It has more features than the XL dataform, but won't require you to learn all the VB to completely cusomize your own userform.

    Steve

  9. #9
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Form vs Form (2000+)

    Hans,

    Thanks. I looked at Jan Karel's activecell and it didn't make much sense. I even rationalized it by thinking something might be different depending on which cell was active when I ran the macro. Activesheet does the trick. I think I even tried that but may have put it in the wrong module. Works now.

    Fred

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: Form vs Form (2000+)

    Steve,

    Thanks for the tip. I'll give it a whirl.

    Fred

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form vs Form (2000+)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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