Excel help please!
Closed
Amygreen85
Posts
3
Registration date
Wednesday August 23, 2017
Status
Member
Last seen
December 19, 2017
-
Aug 23, 2017 at 09:10 AM
Blocked Profile - Aug 23, 2017 at 04:40 PM
Blocked Profile - Aug 23, 2017 at 04:40 PM
Related:
- Excel help please!
- Excel marksheet - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
- Kernel for excel - Download - Backup and recovery
- Excel date format dd.mm.yyyy - Guide
1 response
No. YOu really need to use a database with form entry, and not excel. Excel is great with math calculations and such, not so much for storing data!
Beside, wouldn't your management love to be able to see cool reports based on the data entered?
You can also, form up a FORM in EXCEL, and map the entries to specific cells.
See if this helps:
Making a UserForm in Excel for Data Entry and Display
This is a quick and easy way to create a UserForm for controlling Input into a worksheet in Excel. We will formulate a worksheet for storing the entries, along with creating a quick and easy UserForm for displaying the entries, line by line. And, we will build in the functionality of using the form to Input data into the sheet, also.
Making the UserForm
After Opening a new workbook, and enabling th eDeveloper Tab in the ribbon, open up Visual Basic.
With Visual Basic open, select INSERT and select UserForm.
A new UserForm is created (as Shown).
Now we have create the following elements to handle the work of displaying, entering, and manipulating the display of records, in the forms of Labels, TextBoxs, and Buttons. The elements to create are as follows:
Name---------------------Type-------------------------------------- Usage
UserForm1 UserForm Displays all elements, and houses all code
Label1 Label Displaying Column A values
Label2 Label Displaying Column B values
TextBox1 TextBox Entering in Column A values
TextBox2 TextBox Entering in Column B Values
btnPrevious Button Display previous record
btnNext Button Display the next record
btnAdd Button Adding an entry into the next line
btnSave Button Save the value into the next line
Youshould have a UserForm that looks like this:
We need to write the code that performs all of the magic, now that we have the elements to manipulate, but first we need to enter in a few items into our spreadsheet, and make a Button to display our list.
On "Sheet1" of the workbook, select cell "A1", and enter in "LastName".
On "Sheet1" of the workbook, select cell "B1", and enter in "FirstName".
Now, build a list of names, maybe about 5 to start with, any names will do. Just make certain to enter the last name in the "A" column, and the first name in the "B" column. Take a look at my example below.
Now that we have a listing to display, we need a Button on the spreadsheet to open up the Form for display. To do this, we insert a button onto the sheet, see example. Make certain you select Button1_click as the action. Now we have to build Button1_click.
Select the Spreadsheet, "Sheet1", and on the Developer Ribbon, select Macros.
Select Button1_click, and select Edit. Now, with the action of Button1_Click selected, enter in the following code:
Sub Button1_Click()
UserForm1.Show
End Sub
Now, in the Visual Basic window, select UserForm1 in the Project Browser.
Right Click over the userform in the project Browser and select View Code.
The Nuts & Bolts of the UserForm-"THE CODE"
Now that we have built a spreadsheet with a list of five names in it, a userform with elements to display and enter in data, we need to attach the code to each element to make it all work out. Lets start with Initializing the form. This procedure fires any time the UserForm is displayed, so think of this procedure as the Starting point.
If you recall when you built the Userform, both parts, meaning the "Display"and the "Entry" elements, are displayed at once. This could be confusing to the user, so we need to default the form to only a "Display" form in the beginning. The below picture is an example of a default starting UserForm.
Please notice that the TextBox's are not displayed at this time. That happens because the first thing we do when the the Initialize Procedure runs, is hide the "Entry" elements, makeing the UserForm a "Display" only form. We accomplish the hiding of elements in the functions and routines that follow.
On the UserForm Visual Basic tab, enter in the following code:
Dim thelast
Dim thehighest
This entry reserves memory for the variables. These variables can be accessed by any element on the UserForm.
Now enter in the following initialization routine:
Private Sub UserForm_Initialize()
DisplayRecord
End Sub
Now we need to build the routine of "DisplayRecord". Below the "initializ" routine, add in the following:
Sub DisplayRecord()
CloseEntry
RecordRows
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
In the above code, we run CloseEntry, RecordRows, and then in the last two rows, we grab values off of the spreadsheet to display on the userform. Make certain that you name the Worksheet in your code what ever name you have named the tab on the spreadsheet. In this example, we are keeping with the default name of "Sheet1".
Don't run it all yet, as we still have the CloseEntry, and RecordRows routines to write!
So, above the DisplayRecord() routine, enter in the following routine:
Sub CloseEntry()
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox1.Visible = False
UserForm1.TextBox2.Visible = False
UserForm1.btnSave.Visible = False
End Sub
All that the above routine does, is hide the form elements that we do not wish to see if we are not entering in any data, hence the name of "CloseEntry()". You can see, we clear any value that may be stored into the textbox, and we hide them. HINT, if you wanted to keep the last entry in the textbox, lets say beacuse you have the need to enter the same thing everytime, then eliminate the commands that set the TextBox value to " ".
Now we move on to Recordrows(). Above the CloseEntry() routine, enter in the following code:
Sub RecordRows()
thelast = FindLastrow()
thehighest = thelast
End Sub
Do you remember at the start, when we DIMMED the two above variables? Now we are initializing them with a value. We find the last row in a column, and store it in the value of "thelast". We also then make the variable of thehighest, the same as thelast. This way, we can manipulate the variable of thelast (like an index), without ever loosing track of the highest row available. You will see later on, that we recalculate the two variables after each data entry, so that the variable of "thehighest", will constantly grow with entries.
Take notice of how the variable of thelast is populated. It is returned by a Function called FindLastrow(). This is probably the most asked for piece of VBA on this forum, and here is how we find the last row of a column:
some_variable = Cells(ThisWorkbook.Worksheets("YOUSHEETNAME").Rows.Count, 1).End(xlUp).Row
<CODE>
...this is how it fits into our Program:
<CODE>
Private Function FindLastrow()
cellcount = Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row
FindLastrow = cellcount
End Function
In the above example, we return the value of cellcount through the function itself. Notice [FindLastrow = cellcount], FindLastrow is also the name of the Function.
So, you guessed it, now that we have build the functions to initialize the UserForm correctly, and we know how big our listing is, we need to write and attach the code to give life to the buttons, and make this UserForm, really usable!
Lets start with btnPrevious. Once again, in the UserForm1 General sheet, enter in the following code:
Private Sub btnPrevious_Click()
thelast = thelast - 1
If thelast < 2 Then
MsgBox ("First Record")
thelast = thelast + 1
End If
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
So what does this do? When the user clicks on Previous, it is a given that the user wants to display the previous record. How do we do that? We subtract one the variable of thelast. Remember that when the form is first opened, we count the number of entries on the spreadsheet, find the last row, and display the data. The last row, is also the value of the variable of thelast. So, if we click previous, we must subtract one from the variable of thelast, checking to make certain that the value of thelast is never less than 2, as we are using row 1 as the header row, and we don't need to display the headers on our form. If the value of thelast is ever below 2, we display a message to the user that they have reached the first record (in the form of:)
MsgBox ("First Record")
... and we add another 1 to the variable of thelast. Once agin, the last two lines of code, actually get the data from the sheet, and displays it in the Labels of Label1 and Label2.
That brings us to the Next button. No, I am eally talking about the Next button, named btnNext. In the UserForm1 General sheet, enter in the following code below the previous routine (wow this is starting to get confusing with previous and next, ugg!):
Private Sub btnNext_Click()
thelast = thelast + 1
If thelast > thehighest Then
MsgBox ("Last Record")
thelast = thelast - 1
End If
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
Does it look similar to the previous routine? Yes, I really meant the btnPrevious_Click() routine! It should look familiar, as it is doing the same thing as previous (ugg!), but we are adding, and checking the value against the variable of thehighest. Remember the variable of thehighest, stores the number of rows that are in the spreadsheet, and we never want to display a blank entry in any of our labels, so we make certain we cannot go higher the that value. If we do, we display a message to the user, once again in the form of a MSGBOX, and we then subtract 1 from the variable of thelast. We then pull the data from Sheet1, at the row that corresponds to the value of variable thelast.
SO, at this point, if you wanted, you could go ahead and run this UserForm, and it will display the list that you have entered onto the speadsheet, one row at a time, checking that you never presented with the HEADERS, or a BLANK line with no data in it.
Did you run it? Did it run? If you came up with RUNTIME errors, then make certain you running the Userform against the correct spreadsheet, and that you have already entered in Names into the A and B column. If you ran it without any errors, and you were able to display the data from the sheet, then KUDOS to you, and we can move on!
Lets take a look at the Add button, or btnADD. We are now going to look at what this button does when it is clicked. So go ahead, and in the UserForm1 general sheet, add in the following code:
Private Sub btnAdd_Click()
MakeEntry
End Sub
Yes, we could have put the MakeEntry code into the btnAdd_Click action, but what happens if we want to make other elements able to enter in data? We wouldn't want those items running btnAdd_Click, as they have their own _Click action, and we then would make their _Click action also run MakeEntry. This way, we write one instance of MakeEntry, and as many elements as we want can run that routine!
Now enter in the following:
Sub MakeEntry()
UserForm1.TextBox1.Visible = True
UserForm1.TextBox2.Visible = True
UserForm1.btnSave.Visible = True
End Sub
Here, you should be familiar with the Visible Property of elements. All we are doing is setting the Visible Property of the TextBox's to True, allowing them to be seen on the form. We also make the Save button visible.
So, go ahead and Run the UserForm now. You should now be able to click on Add, and the TextBox's pop into sight. Did they? Did you try to click on the Save button? It didn't work, did it? We havne't built a click action into that button yet. Let's do that now!
Enter in the following code:
Private Sub btnSave_Click()
lastrow = FindLastrow
lastrow = lastrow + 1
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 1).Value = UserForm1.TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 2).Value = UserForm1.TextBox2.Value
DisplayRecord
End Sub
Nothing in the above code should be confusing to anyone at this time. This is still the same old variable manipulation as before, it is just now we are recounting the number of rows to find the last row entry, and adding one to it so we can have a blank row. That is accomplished in the follwoing two lines:
lastrow = FindLastrow
lastrow = lastrow + 1
The next two rows, take the value from the UserForm.TextBox's, and places it onto the spreadhseet at the designated last row, as in:
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 1).Value = UserForm1.TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 2).Value = UserForm1.TextBox2.Value
We then go full circle, just like when the form is opened, and we run the routine of DisplayRecord. We have taken this program full circle, and at this point, it should run, and you should be able to continue to add in as many records as you have space on your harddrive.
--How to make it better?--
What if we had another Label on the UserFrom that displayed the number of Records we have? What variable would you link to that labels caption? What if you had sheets named "Sheet2", "Sheet3", and "Sheet4", and wanted the UserForm to display a particular sheets data based on your selection? What Form element could you use so that they can only select ONE worksheet? Did anyone guess a OptionButton?
Thank you for hanging in there with us, and we hope that you have learned a little about Visual Basic for applications!
Have FUN!
It's kind of fun to do the impossible! -Walter Elias Disney
Beside, wouldn't your management love to be able to see cool reports based on the data entered?
You can also, form up a FORM in EXCEL, and map the entries to specific cells.
See if this helps:
Making a UserForm in Excel for Data Entry and Display
This is a quick and easy way to create a UserForm for controlling Input into a worksheet in Excel. We will formulate a worksheet for storing the entries, along with creating a quick and easy UserForm for displaying the entries, line by line. And, we will build in the functionality of using the form to Input data into the sheet, also.
Making the UserForm
After Opening a new workbook, and enabling th eDeveloper Tab in the ribbon, open up Visual Basic.
With Visual Basic open, select INSERT and select UserForm.
A new UserForm is created (as Shown).
Now we have create the following elements to handle the work of displaying, entering, and manipulating the display of records, in the forms of Labels, TextBoxs, and Buttons. The elements to create are as follows:
Name---------------------Type-------------------------------------- Usage
UserForm1 UserForm Displays all elements, and houses all code
Label1 Label Displaying Column A values
Label2 Label Displaying Column B values
TextBox1 TextBox Entering in Column A values
TextBox2 TextBox Entering in Column B Values
btnPrevious Button Display previous record
btnNext Button Display the next record
btnAdd Button Adding an entry into the next line
btnSave Button Save the value into the next line
Youshould have a UserForm that looks like this:
We need to write the code that performs all of the magic, now that we have the elements to manipulate, but first we need to enter in a few items into our spreadsheet, and make a Button to display our list.
On "Sheet1" of the workbook, select cell "A1", and enter in "LastName".
On "Sheet1" of the workbook, select cell "B1", and enter in "FirstName".
Now, build a list of names, maybe about 5 to start with, any names will do. Just make certain to enter the last name in the "A" column, and the first name in the "B" column. Take a look at my example below.
Now that we have a listing to display, we need a Button on the spreadsheet to open up the Form for display. To do this, we insert a button onto the sheet, see example. Make certain you select Button1_click as the action. Now we have to build Button1_click.
Select the Spreadsheet, "Sheet1", and on the Developer Ribbon, select Macros.
Select Button1_click, and select Edit. Now, with the action of Button1_Click selected, enter in the following code:
Sub Button1_Click()
UserForm1.Show
End Sub
Now, in the Visual Basic window, select UserForm1 in the Project Browser.
Right Click over the userform in the project Browser and select View Code.
The Nuts & Bolts of the UserForm-"THE CODE"
Now that we have built a spreadsheet with a list of five names in it, a userform with elements to display and enter in data, we need to attach the code to each element to make it all work out. Lets start with Initializing the form. This procedure fires any time the UserForm is displayed, so think of this procedure as the Starting point.
If you recall when you built the Userform, both parts, meaning the "Display"and the "Entry" elements, are displayed at once. This could be confusing to the user, so we need to default the form to only a "Display" form in the beginning. The below picture is an example of a default starting UserForm.
Please notice that the TextBox's are not displayed at this time. That happens because the first thing we do when the the Initialize Procedure runs, is hide the "Entry" elements, makeing the UserForm a "Display" only form. We accomplish the hiding of elements in the functions and routines that follow.
On the UserForm Visual Basic tab, enter in the following code:
Dim thelast
Dim thehighest
This entry reserves memory for the variables. These variables can be accessed by any element on the UserForm.
Now enter in the following initialization routine:
Private Sub UserForm_Initialize()
DisplayRecord
End Sub
Now we need to build the routine of "DisplayRecord". Below the "initializ" routine, add in the following:
Sub DisplayRecord()
CloseEntry
RecordRows
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
In the above code, we run CloseEntry, RecordRows, and then in the last two rows, we grab values off of the spreadsheet to display on the userform. Make certain that you name the Worksheet in your code what ever name you have named the tab on the spreadsheet. In this example, we are keeping with the default name of "Sheet1".
Don't run it all yet, as we still have the CloseEntry, and RecordRows routines to write!
So, above the DisplayRecord() routine, enter in the following routine:
Sub CloseEntry()
UserForm1.TextBox1.Value = ""
UserForm1.TextBox2.Value = ""
UserForm1.TextBox1.Visible = False
UserForm1.TextBox2.Visible = False
UserForm1.btnSave.Visible = False
End Sub
All that the above routine does, is hide the form elements that we do not wish to see if we are not entering in any data, hence the name of "CloseEntry()". You can see, we clear any value that may be stored into the textbox, and we hide them. HINT, if you wanted to keep the last entry in the textbox, lets say beacuse you have the need to enter the same thing everytime, then eliminate the commands that set the TextBox value to " ".
Now we move on to Recordrows(). Above the CloseEntry() routine, enter in the following code:
Sub RecordRows()
thelast = FindLastrow()
thehighest = thelast
End Sub
Do you remember at the start, when we DIMMED the two above variables? Now we are initializing them with a value. We find the last row in a column, and store it in the value of "thelast". We also then make the variable of thehighest, the same as thelast. This way, we can manipulate the variable of thelast (like an index), without ever loosing track of the highest row available. You will see later on, that we recalculate the two variables after each data entry, so that the variable of "thehighest", will constantly grow with entries.
Take notice of how the variable of thelast is populated. It is returned by a Function called FindLastrow(). This is probably the most asked for piece of VBA on this forum, and here is how we find the last row of a column:
some_variable = Cells(ThisWorkbook.Worksheets("YOUSHEETNAME").Rows.Count, 1).End(xlUp).Row
<CODE>
...this is how it fits into our Program:
<CODE>
Private Function FindLastrow()
cellcount = Cells(ThisWorkbook.Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row
FindLastrow = cellcount
End Function
In the above example, we return the value of cellcount through the function itself. Notice [FindLastrow = cellcount], FindLastrow is also the name of the Function.
So, you guessed it, now that we have build the functions to initialize the UserForm correctly, and we know how big our listing is, we need to write and attach the code to give life to the buttons, and make this UserForm, really usable!
Lets start with btnPrevious. Once again, in the UserForm1 General sheet, enter in the following code:
Private Sub btnPrevious_Click()
thelast = thelast - 1
If thelast < 2 Then
MsgBox ("First Record")
thelast = thelast + 1
End If
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
So what does this do? When the user clicks on Previous, it is a given that the user wants to display the previous record. How do we do that? We subtract one the variable of thelast. Remember that when the form is first opened, we count the number of entries on the spreadsheet, find the last row, and display the data. The last row, is also the value of the variable of thelast. So, if we click previous, we must subtract one from the variable of thelast, checking to make certain that the value of thelast is never less than 2, as we are using row 1 as the header row, and we don't need to display the headers on our form. If the value of thelast is ever below 2, we display a message to the user that they have reached the first record (in the form of:)
MsgBox ("First Record")
... and we add another 1 to the variable of thelast. Once agin, the last two lines of code, actually get the data from the sheet, and displays it in the Labels of Label1 and Label2.
That brings us to the Next button. No, I am eally talking about the Next button, named btnNext. In the UserForm1 General sheet, enter in the following code below the previous routine (wow this is starting to get confusing with previous and next, ugg!):
Private Sub btnNext_Click()
thelast = thelast + 1
If thelast > thehighest Then
MsgBox ("Last Record")
thelast = thelast - 1
End If
UserForm1.Label1.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 1).Value
UserForm1.Label2.Caption = ThisWorkbook.Worksheets("Sheet1").Cells(thelast, 2).Value
End Sub
Does it look similar to the previous routine? Yes, I really meant the btnPrevious_Click() routine! It should look familiar, as it is doing the same thing as previous (ugg!), but we are adding, and checking the value against the variable of thehighest. Remember the variable of thehighest, stores the number of rows that are in the spreadsheet, and we never want to display a blank entry in any of our labels, so we make certain we cannot go higher the that value. If we do, we display a message to the user, once again in the form of a MSGBOX, and we then subtract 1 from the variable of thelast. We then pull the data from Sheet1, at the row that corresponds to the value of variable thelast.
SO, at this point, if you wanted, you could go ahead and run this UserForm, and it will display the list that you have entered onto the speadsheet, one row at a time, checking that you never presented with the HEADERS, or a BLANK line with no data in it.
Did you run it? Did it run? If you came up with RUNTIME errors, then make certain you running the Userform against the correct spreadsheet, and that you have already entered in Names into the A and B column. If you ran it without any errors, and you were able to display the data from the sheet, then KUDOS to you, and we can move on!
Lets take a look at the Add button, or btnADD. We are now going to look at what this button does when it is clicked. So go ahead, and in the UserForm1 general sheet, add in the following code:
Private Sub btnAdd_Click()
MakeEntry
End Sub
Yes, we could have put the MakeEntry code into the btnAdd_Click action, but what happens if we want to make other elements able to enter in data? We wouldn't want those items running btnAdd_Click, as they have their own _Click action, and we then would make their _Click action also run MakeEntry. This way, we write one instance of MakeEntry, and as many elements as we want can run that routine!
Now enter in the following:
Sub MakeEntry()
UserForm1.TextBox1.Visible = True
UserForm1.TextBox2.Visible = True
UserForm1.btnSave.Visible = True
End Sub
Here, you should be familiar with the Visible Property of elements. All we are doing is setting the Visible Property of the TextBox's to True, allowing them to be seen on the form. We also make the Save button visible.
So, go ahead and Run the UserForm now. You should now be able to click on Add, and the TextBox's pop into sight. Did they? Did you try to click on the Save button? It didn't work, did it? We havne't built a click action into that button yet. Let's do that now!
Enter in the following code:
Private Sub btnSave_Click()
lastrow = FindLastrow
lastrow = lastrow + 1
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 1).Value = UserForm1.TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 2).Value = UserForm1.TextBox2.Value
DisplayRecord
End Sub
Nothing in the above code should be confusing to anyone at this time. This is still the same old variable manipulation as before, it is just now we are recounting the number of rows to find the last row entry, and adding one to it so we can have a blank row. That is accomplished in the follwoing two lines:
lastrow = FindLastrow
lastrow = lastrow + 1
The next two rows, take the value from the UserForm.TextBox's, and places it onto the spreadhseet at the designated last row, as in:
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 1).Value = UserForm1.TextBox1.Value
ThisWorkbook.Worksheets("Sheet1").Cells(lastrow, 2).Value = UserForm1.TextBox2.Value
We then go full circle, just like when the form is opened, and we run the routine of DisplayRecord. We have taken this program full circle, and at this point, it should run, and you should be able to continue to add in as many records as you have space on your harddrive.
--How to make it better?--
What if we had another Label on the UserFrom that displayed the number of Records we have? What variable would you link to that labels caption? What if you had sheets named "Sheet2", "Sheet3", and "Sheet4", and wanted the UserForm to display a particular sheets data based on your selection? What Form element could you use so that they can only select ONE worksheet? Did anyone guess a OptionButton?
Thank you for hanging in there with us, and we hope that you have learned a little about Visual Basic for applications!
Have FUN!
It's kind of fun to do the impossible! -Walter Elias Disney
