Dec 29 2020 03:58 AM
Hi, I have been asked by someone as to whether it's possible, and if yes; can I please attempt to make one for him, to create a userform on excel that stores the data. It's not like a query where the userform is emptied every time you open it so it can be filled in and saved on the spreadsheet. I want to know if it's possible on Excel (I've seen it several times on access templates but I'm unsure how to do it on excel) to make it interactive and the info is saved on it so I can just click on it and bee able to see/edit the data.
Furthermore, I know that command buttons can be used to open the userform, but can I use the same command button to close it, all the while that the userform is still open??
Help would be much appreciated,
Thank you very much
Dec 29 2020 04:33 AM
A userform is volatile - when you close it, the values of its text boxes etc. are gone. So if you want to keep those values, you have to store them elsewhere - in cells on a worksheet, or in custom document properties, or in the Windows Registry (if you're on Windows), or in a separate file.
By default, a userform is modal. This means that the user cannot interact directly with the workbook while the userform is open. So the user cannot click on a command button on a worksheet.
It is possible to open a userform modeless. The user can then interact with the workbook while the userform is open, including clicking on a command button. It might be confusing if the user edits cells while the userform is open; it's up to you to decide whether that is a problem.
Dec 30 2020 05:31 AM
Thank you very much for replying so promptly.
I'm not sure if you implied or did not as to whether you know how to or not, but do you know how I can store info on, say, the spreadsheet which can then be viewed on a userform as well, so it looks like it's being stored on the userform. And if I were to change something, then the info on the spreadsheet will also update itself.
Also, could you explain to me how to make a userform modeless so I can interact with the workbook while the userform(s) are open? I have included a template database sheet that 'stores' data inserted into it.
Dec 30 2020 06:04 AM
SolutionI don't see an attachment in your reply.
See the attached workbook. The userform is displayed modeless, and it gets its data from and stores them on the Data sheet.
Dec 31 2020 02:43 PM
Once again, thank you very much for your time and invaluably helpful information
Would you be able to explain how you made the form so that I can still interact with the actual spreadsheet while the userform is open?
Thank you very much
Dec 31 2020 04:07 PM
The macro in Module1 that opens the userform has this line:
UserForm1.Show False
The False argument tells Excel to open the userform modeless.
I also set the ShowModal property of the userform to False, so that it is opened modeless when you run it directly from the Visual Basic Editor.
Jan 02 2021 02:06 PM
Thank you very much:thumbs_up: I really appreciate it
Just a few more questions...
Do you know how to create a userform on excel that not just opens on the press of a chosen button, but also closes by pressing that very same button, only it's programmed to open the userform when it's closed and closes the userform when it's open?
Additionally, do you know how to make an option on the userform to insert pics onto it?
And also, you kindly showed me how to make it that the userform stores data, the problem with that is that if I change something on it, I don't have the option to cancel it as the userform saves it straight away. Do you know how to make a 'save' and 'cancel' button that influences the data inserted.
And one last thing (until I come up with another problem that I need your expertise for) do you know how to make a macro that repeats the procedure every time I want it to. For instance, if I wanted to make a sheet on shops and I make a shape on excel that opens the userform when clicked on and refers to 'shop 1', and hopefully closes when I click on that shop 1 button if the form is open. do you know how to make it so that if I click on a button, a new shape or cell comes up that refers to 'shop 2' and has it's own userform and stores its data elsewhere and If i were to click it again, it would open 'shop 3' etc. I have included a 'desktop students database template' so you can see what i mean and perhaps infer from it that which I couldn't
Thank you very much
Jan 02 2021 02:09 PM
Sorry, it ddnt allow me to include for some reason, but if you want to see it, search 'students' on access templates
Jan 03 2021 04:08 AM
ok, i really appreciate all the help you've given me till now. if you find out how to do anything I requested, id greatly appreciate if you could share it with me
Thank you very much
Jan 03 2021 05:04 AM
In the attached version, the button on Sheet1 can be used to show the userform and to close it.
And the userform now has Save and Cancel buttons.
The rest of your questions is too much.
Jan 03 2021 09:53 AM
Wow! That's almost all I asked for! Thanx
Could you explain how you did it as I don't u understand the code, or refer me to somewhere that explains it
Thank you very, very much
The rest I will prob repost and see if anyone else answers
Jan 03 2021 11:25 AM
@Shaya5 The macro executed by the button on Sheet1 checks whether the userform is loaded. If not, it displays the userform and changes the caption of the button to "Close UserForm". If the form was already loaded, it unloads it.
In the previous version, the ControlSource of the text boxes on the userform was set to cells B1 to B3 on the Data sheet. In the new version, I cleared the ControlSource.
Instead, the text boxes are filled in the UserForm_Initialize event procedure, and the result is written back to the Data sheet in the cmdSave_Click event procedure.
The UserForm_QueryClose event procedure is executed when the userform is closed. It resets the caption of the button on Sheet1 to "Show UserForm".
Jan 04 2021 08:56 AM
Thank you very much
I saw that when you renamed the caption of that button that begins the whole chain when first you called it "show..." and then "close...", in the code you called it 'Button 1.' How can I find out the name oof the button so I can program exactly which button I want?
Jan 04 2021 02:00 PM
Right-click the button. You'll see its name in the Name box on the left hand side of the formula bar.
Jan 05 2021 09:27 AM
i only managed to find the name of the button when it is a command button. but when I make a shape to which I connect the form to, it doesnt let me see its name. do you have any solutions?
Thank you very much
Jan 05 2021 12:33 PM
Right-clicking on a shape should work too.
Alternatively, click Find & Select > Selection Pane on the Home tab of the ribbon.
You will see the names of all shapes on the active sheet. You can click on a name to select the shape.
Jan 07 2021 07:30 AM
Thank you. I managed to find the name. however, when I substituted the 'button 1' to the name of my new shape 'Rectangle: Rounded Corners 1,' it came up with an error when I tried opening it. would you have any idea why???
Thanx
Jan 07 2021 08:23 AM
That is because a rectangle is not a button, and because its actual name is probably Rounded Rectangle 1. You'll have to use
Sheet1.Shapes("Rounded Rectangle 1").TextFrame.Characters.Text = "Show UserForm"
and
Sheet1.Shapes("Rounded Rectangle 1").TextFrame.Characters.Text = "Close UserForm"
Dec 30 2020 06:04 AM
SolutionI don't see an attachment in your reply.
See the attached workbook. The userform is displayed modeless, and it gets its data from and stores them on the Data sheet.