User Form

[daddyfoxuk]

Hi all, would anybody be willing to help me create a user form... I already have an excel sheet created with various vba codes and macros but I’m looking at trying to go that one step further. Unless anyone has any other suggestions...? Thanks.

 

[Tim_Excel_]

I don't think you're giving much information to enable us to help you.. And Google is your best friend, there is a tonne of information out there on making a UserForm.
On your Workbook, press Alt+F11. Right click on your project explorer > insert > UserForm

 

[Kamolga]

Hi all, would anybody be willing to help me create a user form... I already have an excel sheet created with various vba codes and macros but I’m looking at trying to go that one step further. Unless anyone has any other suggestions...? Thanks.

Sorry but that is too vague. You need to explain why you want a userform (I guess this is to force user to enter certain data in a specific way or/and to allow user to go throug all kind of information without any knowledge of Excel).

You would need to start drawing it (a textbox for data entry, a button to do xxx, a list to chose yyy), and by doing so you will go through all possible scenarios the user can face...and then only we can help with specific coding question (such as how can I force the user to only enter numbers in a textbox, how can I send the value of a textbox to a cell, etc.)

 

[daddyfoxuk]

Ok, so to explain a little further... I have an excel sheet which contains a list of several hundred numbers of which we need to find. We scan each part and if(iserrror(match( will return if that part is in the list, if it is a input box appears telling you it is and requires a password to continue... which afterwards a couple of command buttons create 2 separate list depending on if we need that part or not. This then fires out emails showing which part has been found, hope this makes sense. It would be nice to just see a user form and everything hidden but I have no experience with these... so any advice is much appreciated.

[TABLE="width: 1360"]
<colgroup><col width="225" style="width: 169pt; mso-width-source: userset; mso-width-alt: 8228;"> <col width="115" style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;"> <col width="230" style="width: 173pt; mso-width-source: userset; mso-width-alt: 8411;"> <col width="194" style="width: 146pt; mso-width-source: userset; mso-width-alt: 7094;"> <col width="421" style="width: 316pt; mso-width-source: userset; mso-width-alt: 15396;"> <col width="193" style="width: 145pt; mso-width-source: userset; mso-width-alt: 7058;"> <col width="206" style="width: 155pt; mso-width-source: userset; mso-width-alt: 7533;"> <col width="133" style="width: 100pt; mso-width-source: userset; mso-width-alt: 4864;"> <col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <tbody>[TR]
[TD="width: 225, bgcolor: #5B9BD5"] List
[/TD]
[TD="width: 115, bgcolor: #5B9BD5"]Pallet
[/TD]
[TD="width: 230, bgcolor: #5B9BD5"]Scan Part Below[/TD]
[TD="width: 194, bgcolor: #5B9BD5"]Search
[/TD]
[TD="width: 421, bgcolor: #5B9BD5"]Action[/TD]
[TD="width: 193, bgcolor: #5B9BD5"]Part
[/TD]
[TD="width: 206, bgcolor: #5B9BD5"]Pallet
[/TD]
[TD="width: 133, bgcolor: #5B9BD5"]Inspected By[/TD]
[TD="width: 93, bgcolor: #5B9BD5"]Time Stamp[/TD]
[/TR]
[TR]
[TD="bgcolor: #D0CECE"]1
[/TD]
[TD="bgcolor: #D0CECE"] [/TD]
[TD="bgcolor: #D0CECE"]
[/TD]
[TD]Please Scan Part
[/TD]
[TD]Please Scan Part[/TD]
[TD="bgcolor: #D0CECE"] [/TD]
[TD="bgcolor: #D0CECE"] [/TD]
[TD="bgcolor: #D0CECE"] [/TD]
[TD="bgcolor: #D0CECE"]27/11/2018 11:17[/TD]
[/TR]
</tbody>[/TABLE]

The sheet looks like te aoe but on te left we have 100's of numbers....

 

[Kamolga]

To me this is a good case for userform but this is a full project. The only advice we can give is to use google step by step. You will have to spend hours into it.

As a example, when you type "vba hide excel" you quickly come to this...

Private Sub UserForm_Activate()
Application.Visible = False
End Sub

but the first step is to find how to draw a userform, how to show and hide them (easy userform1.show, but how do you trigger the .hide? OK button?) and then you should find where to use Private Sub UserForm_Activate() codes.

With that, 50% is done. Then comes the searching code in itself, the one for email, etc... You don't need prior knowledge even if knowing events (for example launch a macro when workbook opens) helps as this exactly the same principle

 

[daddyfoxuk]

Hi, I have all the codes working within the sheet at the moment through vba... could i just use these on the user form...? As i said i have no experice with these but currently the sheet is working perfectly using vba. Thanks forth advice

 

[Kamolga]

Hi, I have all the codes working within the sheet at the moment through vba... could i just use these on the user form...? As i said i have no experice with these but currently the sheet is working perfectly using vba. Thanks forth advice

Then it is easy

Private Sub CommandButton1_Click()
'Your code
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'your code
End Sub

but those are not in module, they are in userform. Again, nobody can draw a userform and assign the macros for you, you will have to learn it (https://www.youtube.com/watch?v=TxQU8qj4K_Y)