Excel Macros UserForms - Learn Excel Macros in simple and easy steps starting from basic to advanced concepts with examples including Overview, Creation, Macros in a Single Workbook, Security, Absolute References, Relative References, VBA, Understanding Codes, Assigning Macros to Objects, Running a Macro, Creating a Macro Using VBA Editor, Editing, UserForms, Debugging a Code, Configuring a Macro. In Microsoft Excel VBA, you may like to have similar forms having identical size and elements. It would be easy if forms can be copied,lot of work and time can be saved. But you could not right click on the userform to make a copy. There is an easy work around. Just follow following simple steps.
Just throw some light in to some issues related to this control.Date picker is not a standard control that comes with office package. So developers encountered issues like missing date picker controls when application deployed in some other machiens/versions of office. In order to use it you have to activate the reference to the.dll,.ocx file that contains it.In the event of a missing date picker, you have to replace MSCOMCT2.OCX file in System or System32 directory and register it properly. The proper replacement of the file.In the VBA editor menu bar- select tools- references and then find the date picker reference and check it.If you need the file, download MSCOMCT2.OCX from.
.TextBoxes on userforms in Excel VBAWhen you work with VBA macros in Excel, you often need user input via a userform. It can be text (e.g. A name) or numeric values, and the TextBox control is ideal for this.You put one or more text boxes on your userform and ask the user for input. Afterwards you have to check, if the user 'followed the rules'. If you just assume that he typed a numeric value, your program will crash, if he wrote something else.Below is a simple example on how to check the content of a textbox.
That is laborious, if you have a lot of textboxes to check the same way, so afterwards I show how to check the smart way using.To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.The simple controlImagine a userform with a textbox (TextBox1) and a command button. The user is asked to type a numeric value in the textbox and then click the OK button.To check the user input you put the following code in the command button's click procedure, 'Private Sub CommandButton1Click'.First we check if the textbox contains a numeric value or is empty. If it isn't a numeric value, we delete the content, place the cursor in the textbox (the.SetFocus command) and keep the userform open.If it is a numeric value, we convert it to the data type Double, insert the value in cell A1 on the active worksheet and close the userform.We must convert to the data type Double, because the content of a textbox is always a String.
If you don't convert it, things will go wrong, if you want to use the number in a calculation. 'And here is the click procedure for our command buttonPrivate Sub CommandButton1Click'Runs when the user clicks the command button.' Write some code here if you want any'action before the userform closes.' Now the collection of textbox classes is'set = Nothing to free memory.Set InputNbCol = Nothing'Close the userformUnload MeEnd SubAnd now for the class moduleMake a class module (in the VBA editor menu you select 'Insert' and 'Class Module'). In the class module's properties window you change the name to 'clTextBoxClass'.
If the properties window isn't visible, select it in the menu 'View' or press F4.Having done that you copy the code below and paste into the class module. But first I had better explain what goes on in the class module.In this example we accept nothing but numeric values, decimal separator (.) and minus (for negative values). This is controlled by the procedure. Private Sub InputTextBoxkeypress (ByVal KeyAscii As MSForms.ReturnInteger)which is called every time the user presses a key. We check the character's Ascii code and accept numeric values only (and '.' And '-').Of course you can check the same way if for instance you only accept upper case.
You can see how that would look.However the Keypress procedure only captures 'normal' keys being pressed (ANSI characters), so if a user pastes a copied text into the textbox with CTRL+V, he can cheat us.Therefore the class will also prevent the user from pasting text into a textbox. The way it does this is neither sophisticated nor elegant, but it works.The prevent-paste trick lies in the procedure. Option ExplicitPublic WithEvents InputTextBox As MSForms.TextBoxPrivate mvarPaste As BooleanPrivate mvarText As String'.Private Sub InputTextBoxkeypress (ByVal KeyAscii As MSForms.ReturnInteger)'Allows nothing but numbers, dots and minus.' Ascii 46 is a dot, Ascii 45 is minus.