input template

  • Thread starter Thread starter mike allen
  • Start date Start date
M

mike allen

i have seen formatting that looks like a lightswitch cover in a house: a
plastic plate w/ rectangular holes where the lightswitches come out. on a
spreadsheet, this is used to make it obvious where you are to input data
(the rectangular holes, or cells) and obvious where you are NOT supposed to
input data (the plastic plate, or protected unwritable cells).

say i want to only allow inputs in cells a4, a5, d4, and d5 and not allow
inputs in any other cell on the sheet. i know i can protect all but these
4, then shade w/ colors, bold, etc. but it doesn't look as good/professional
as this "plate" effect. any thoughts? thanks, mike allen
 
It sounds like you are describing userform with textboxes - If you know what
a userform its, then maybe not, but that is what it sounds like.
 
Know it is totally related to Excel and Office. It puts up a dialog that
looks like a light switch plate and the textboxes look like where the switch
goes - how ever it isn't used as a mask for the worksheet - the information
is typed in the holes and the code transfers the information to the
appropriate cells.



http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms


http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en-us/dnoffpro01/html/IntroductiontoUserFo
rmsPartI.asp
Part II
http://msdn.microsoft.com/library/en-us/dnoffsol02/html/IntroductiontoUserFo
rmsPartII.asp
 
Know it is totally related to Excel and Office.

should be

No, it is totally related to Excel and Office.
 
Hi Mike,

I think I know what you want. It's been so long that I
did it that way, that I had to spend some time to figure
out what I used to do, many years ago.

What you do is unlock the protection of the cells that you
want to enter data into (like B5, D5 etc.). This is under
Format Cells. Take the check mark off of Locked under the
Protection Tab. You color the cells that you don't want
the user to enter data into (say light blue), and leave
the cells that are unprotected with a different color (say
white), so that the user will be forced to enter the data
there, for it to function properly. (Also put in borders
and written instructions etc., if you would like.) After
doing all that, then you protect the sheet with a
password. See the menu above, Tools-Protection.

After a while, that method may no longer be needed, if you
use a UserForm (which has been mentioned). This is
created by hitting ALT-F11. Click on the Workbook name at
the very top on the left (if you have more than one
workbook open), and push "Insert" above.
Click "UserForm". Name the form in the properties at the
very top. See where it is called UserForm1 on the right
side of (Name). Change that to frmSample (just for the
heck of it and for this exercise to work). Now just to
the right side of the Caption in the properties, change
the UserForm1 to "My First Form" (again just for the heck
of it...).

Now click the Workbook name at the top-left again, and
push "Insert". Then click "Module" this time.

Write this in your module.

Sub SampleForm()
frmSample.Show vbModeless
End Sub

Now go back to your worksheets by pushing ALT-F11 again.
Go to the menu above and push View-Toolbars-Forms. You'll
see a tiny rectangular gray button on this toolbar. Click
this button and draw it on the sheet in a cell that you'd
like. To get the button to fit a cell or cells exactly,
hold down the ALT key. When you finish drawing this
button, it will ask you for a macro. Pick SampleForm.

Now you can push the button that you created on your
worksheet, and the form will appear. If you want to add
text boxes and other button etc. to the form, you'll have
to read and ask a lot more questions. This is just to get
you started. This is only a sample for those just
beginning to learn...there's better ways...that you'll
learn later.

I hope this helps. If you understand what I wrote, and
want people to help you further with the UserForm, I'm
sure that they could help you, by creating text boxes etc.
(to make your switch box). You might want to start with
reading some...from a lot of good VBA programming books
available for Excel users.

Happy Holidays!
 
Back
Top