Form Development (2k)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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]

Reply | Quote

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, …)

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 this Excel thread.

Reply | Quote

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.

Reply | Quote

i this forum. you guys are the best, thanks so much everyone!! Rory, Brent, thanks for the examples and John thanks for the additional info!

Reply | Quote