Using userform to input data

I'm trying to create and use a userform to input data into specific cells in a sheet. For instance, if I create the userform with 4 text boxes and I want the user to input numbers into those text boxes that then fill in a hidden sheet.

"Textbox A" = cell A2 on sheet 1
"Textbox B" = cell B2 on sheet 1
etc.

I actually have 8 boxes for the user to input numbers (some are percentages, some are dollar amounts) and then an update button.

How do I do this?

Hello SoCalRizzy,

The basic code is to load a TextBox with a cell's contents is this...

Please Login or Register  to view this content.

Since the TextBox can only accept a string value the Text property of the cell is used instead of the Value property. Reverse the code to copy the value of the TextBox into a cell.

Sincerely,
Leith Ross

If someone could take a look at this to see what I'm trying to do. If you open up VBE you can see the userform I'm trying to create. There are 6 text boxes that need to be filled out and then an "Update" button to update the corresponding cells with what was entered. I'm VERY new to VBA so I have no idea how to do this. Any help would be greatly appreciated.

Hello SoCalRizzy,

Which cells will the TextBox data be updating? Column "B"?

Sincerely,
Leith Ross

Hello SoCalRizzy,

I added code to the UserForm to copy the data from the TextBoxes to the worksheet cells in column "B". Not sure if I got all the percentages and dollar amounts in the right place, but it is a start. The data is transfered after the pressing enter, instead of using the update button. This want to change this, let me know.

Sincerely,
Leith Ross

This is wonderful so far! I was hoping I would be able to figure it out now by looking at the code, but it's still somewhat confusing. How hard is it to have the "Cancel" and "Update" buttons work?

Hello SoCalRizzy,

Do you want the the "Cancel" button to close the form? Do You want to "Update" when the button is pressed rather than updating when a entry is made?

Sincerely,
Leith Ross

Yes, the cancel button should just cancel all inputs and close the form. The "update" button should update the cells with what was entered and then close the box. Thank you so much! This has been a huge help!

Hello SoCalRizzy,

I made the changes to the attached workbook. Let me know if you need anything else.

Sincerely,
Leith Ross

This is awesome! Thank you so much for your help! I can definitely use this and build upon it.

Now, related to this worksheet. I know there's a way to password protect the sheet as well as hide that specific worksheet. But I don't want people to be able to unhide the sheet. Is that possible?

Hello SoCalRizzy,

You can but it has to be done using VBA code.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

Where to I add this code? Also, what code do I add to the userform to password protect the button? For example, I want a password to come up when you click on the button to show the userform.

Thanks!

hi,

you will need to create another form for the password prompt to have your other form hidden when click the show form button from your sample workbook. I have placed the password protection form. You may try it. The password is: Password.

Where do I put this code in the sheet? I'm done editing and making all the changes and incorporated a password when opening the userform.

Originally Posted by Leith Ross

Hello SoCalRizzy,

You can but it has to be done using VBA code.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

hi,

you can also hide the sheet that you would like to be hidden by setting the Visible property to 2-xlsheetveryhidden. to do this, on the vba editor, click the sheet that you would like to be hidden and then click the drop-down from the property window of the sheet that you would like to be hidden.

Hope that helps.

Burke