SOLVED

Excel userforms

Copper Contributor

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

22 Replies

@Shaya5 

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.

@Hans Vogelaar 

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.

best response confirmed by Shaya5 (Copper Contributor)
Solution

@Shaya5 

I 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.

@Hans Vogelaar 

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

@Shaya5 

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.

@Hans Vogelaar 

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

 

@Hans Vogelaar 

Sorry, it ddnt allow me to include for some reason, but if you want to see it, search 'students' on access templates

@Shaya5 

I'm afraid you're asking too much now. Sorry.

@Hans Vogelaar 

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

@Shaya5 

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.

@Hans Vogelaar 

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

@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".

@Hans Vogelaar 

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?

@Shaya5 

Right-click the button. You'll see its name in the Name box on the left hand side of the formula bar.

@Hans Vogelaar 

Cheers

I really appreciate it

@Hans Vogelaar 

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

@Shaya5 

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.

@Hans Vogelaar

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

@Shaya5 

 

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"

1 best response

Accepted Solutions
best response confirmed by Shaya5 (Copper Contributor)
Solution

@Shaya5 

I 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.

View solution in original post