Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Development (2k)

    I never really used excel past its built in functions/spreadsheet capabilities... but I'm going to try and learn how to use VBA for it. So what I'm going to do is make a unit conversion tool using some of the VBA controls like combo boxes. I have a couple of questions though, like what do I use as my "form" back drop? I was thinking just using the paint tool bar - rectangle, or just painting the cell backgrounds a solid color. Is there a better way of doing this? 2nd question: how do I "lock" things so people can't edit it or accidentally clear cells w/ code?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  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: Form Development (2k)

    John,
    You could use a userform for this rather than trying to simulate a form with a worksheet. If you do want to use a worksheet, you could hide gridlines and format all cells with a fill colour. You can also lock and unlock cells, though this only takes effect when you protect the worksheet.
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Form Development (2k)

    1. If you want to do this in a worksheet itself, use the cell background (Format | Cells..., Patterns tab, or the Fill Color dropdown on the Formatting toolbar)
    2. Locking cells in Excel is a two-step process:
    - Select the cells you want to be unlocked, then select Format | Cells..., activate the Protection tab and clear the Locked check box. Click OK.
    - Select Tools | Protection | Protect Sheet..., specify a password if you like, then click OK.

    You could also create a Userform in the VBA editor.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Development (2k)

    how do you make a user form? I can see how to make controls, but not an actual form.. Right now, this is what I have
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  5. #5
    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: Form Development (2k)

    Open the VB Editor (Alt+f11) and then choose Insert-Userform.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Development (2k)

    got it. is there any point in making this in excel if i have vb? is excel just used as an alternative if someone doesn't have VB and doesnt want to shell out the cash for it? - just trying to figure out the reasoning of user forms and what not of VBA for excel.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form Development (2k)

    It depends. Controls on a userform in Excel can be tied to worksheet cells, so you can use the Excel engine for calculations. But an "application" built with Excel VBA only works within Excel, it is not a standalone application.
    An application built in VB6 is (more or less) a stand-alone application, people do not need to have Excel to run it, but you don't have the benefit of Excel formulas.

  8. #8
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Development (2k)

    ok, thanks everyone. 1 last question: know of any resources where I could find examples of macros or userforms or whatever? i'm having a bit of trouble with the syntax in VBA, not sure how the controls really work, and calling their properties.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

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

    Re: Form Development (2k)

    I get all kinds of weird errors when I try to open the workbook you attached, either directly or after downloading. (Path not valid, object not found, object removed, ...)

  10. #10
    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: Form Development (2k)

    Hans,
    Thanks for pointing that out - so do I now in both that version and the original so I have removed it from my post and will try and create a new one.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Form Development (2k)

    I know your queston is about VBA and userforms (in which I just had a crash course thanks to Rory), but you might also first check <!post=this Excel thread,427285>this Excel thread<!/post>.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Long Beach, California, USA
    Posts
    233
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Development (2k)

    John,

    Some of my users have a need to expand a list of values a certain number of times based upon a time interval. This form is called from a custom menu in Excel. I copied the form and .bas module into a workbook so you could run this.

    Brent

    edit: the form doesn't want to run from the web. save to your computer and run - works much better.

  13. #13
    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: Form Development (2k)

    Just to add to what Hans said, for something like this, Excel may be easier in terms of setting up and maintaining since you can link listboxes to cell ranges so that they populate automatically etc. and you can also see exactly what your base data is quite readily, as well as alter it. In addition, if you have the Analysis Toolpak add-in loaded, there are a whole load of built-in conversion functions you can use. I've attached a quick sample, which doesn't use the add-in, showing how you can use data tables and lists.

    [Edit: after Hans' response, I have discovered that the workbook appears to have become corrupted so I am removing it. I will try and create a new working copy! - Rory]

    [Later: Attached version hopefully now fixed! - Rory]
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Development (2k)

    i <img src=/S/heart.gif border=0 alt=heart width=15 height=15> this forum. you guys are the best, thanks so much everyone!! Rory, Brent, thanks for the examples and John thanks for the additional info!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

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