10.11 Save New Details - Transfer the data to the spreadsheet

Ongoing tutorial - First part is here: Part One

To add a new entry onto the spreadsheet, the Save New Details button needs to be clicked. The coding for this is fairly straightforward as we're only transferring the form details to cells on the spreadsheet. We need to do some error checking, however, just to make sure the correct data gets transferred.

Return to your form and double click your cmdSave button to open up a code stub.

The first thing we need to do is to select the next empty row on the spreadsheet. If you have a look it again you'll see that this is row 6 for us:

After data gets added to row 6, the next empty row will be row 7, and so on.

Here's the code that selects the first empty cell in Column A (careful of the space after UsedRange, if you're copying and pasting):

LastRow = ActiveSheet.UsedRange. SpecialCells(xlCellTypeLastCell).Row

Cells(LastRow + 1, 1).Select

You've met this code before, in a previous section. It searches for the last row on the spreadsheet that has data in it. This row number is then stored in the LastRow variable. The second line then uses this row number between the round brackets of Cells. We need to add 1 to the LastRow variable to get the next empty row down.

TextBox Error Check

For the textboxes on the form, we'll only do some rudimentary error checking. We'll just make sure that they're not blank.

Add the following function to your code:

Private Function IsBlankTextBox( ) As Boolean

End Function

We've called this function IsBlankTextBox. The return type for the function is As Boolean.

The first line to add to your function is this:

IsBlankTextBox = False

All this does is to set the function to a value of False. The rest of the function will check each textbox in turn to see if they are blank or not. If they are blank then we can set IsBlankTextBox to True. We can then exit the function and display an error message. Here's the code for the first textbox:

If tbImageName.Text = "" Then

IsBlankTextBox = True
Exit Function

End If

To check for a blank textbox we only need a pair of double quotes with no space between them. This goes after the equal sign. Before the equal sign we just need the name of a textbox.

Here's the entire code for the function:

You can copy and paste the code for the first one, and then just change the name of each textbox.

To call this function into action, return to your cmdSave button. Now add this:

Dim IsEmptyTextBox As Boolean
IsEmptyTextBox = IsBlankTextBox( )

If IsEmptyTextBox = True Then

MsgBox "Empty TextBox Detected"
Exit Sub

End If

The first line sets up a Boolean variable called IsEmptyTextBox. The next line is this:

IsEmptyTextBox = IsBlankTextBox( )

To the right of the equal sign is the call to our function. When the function is executed, VBA returns with an answer. This will be True if there is an empty textbox and False if everything is OK. The If Statement checks for this value:

If IsEmptyTextBox = True Then

If an empty text box is found then we display an error and Exit the Sub.

ComboBox Error Check

Our ComboBox has the default text Camera. We set this up when we added the ComboBox to the form and typed something for the Text property. If you want to know whatever a user selected from your dropdown ComboBoxes then the property to use is this same Text property. The code for us is this:

If ComboBox1.Text = "Camera" Then

MsgBox "No Camera selected"
Exit Sub

End If

What we're saying here is that if the Text property of ComboBox1 is equal to "Camera" then execute some code. The code we want to execute is a message box telling the user that no camera was selected from the dropdown list. We then Exit the Sub.

That's all that's needed for the ComboBox error checking.

Option Button Error Checking

We have two option buttons on the form, one for Yes and one for No. We want at least one of them selected. If no option button is selected then we need to flag this as an error.

To check if an option button is selected you can use the Value property. This will be either True (selected) or False (not selected). We can do both in and If Statement, with the word And between the two. Like this:

If OptionButton3.Value = False And OptionButton4.Value = False Then

MsgBox "No Option button selected"
Exit Sub

End If

This code just checks if OptionButton3 and OptionButton4 are both False. If they are then it means no button was selected. In which case, we display a message box and Exit Sub.

If everything is OK from all our error checking above then we can go ahead and transfer the data from the form to the spreadsheet.

Transferring Form Data to a Spreadsheet

To transfer the data from the textboxes on the form to the cells on the spreadsheet, we can use Offset:

ActiveCell.Value = tbImageName.Text
ActiveCell.Offset(, 1).Value = tbDateTaken.Text
ActiveCell.Offset(, 2).Value = tbInfo.Text
ActiveCell.Offset(, 3).Value = tbDimensions.Text
ActiveCell.Offset(, 4).Value = tbImageSize.Text

To the right of the equal sign, we have the name of a textbox followed by the Text property. To the left of the equal signs, we have ActiveCell. The first one is the cell that is currently selected, the one in the A column. We can put the image name in here. The other four have Offset values. We use Offset for the columns because we want to go across filling each cell.

To get the value from the Camera ComboBox, we can access the Text property, just like we did for the textboxes:

ActiveCell.Offset(, 5).Value = ComboBox1.Text

To get the correct value from the option buttons, the code is slightly more complex. It's this:

If OptionButton3.Value = True Then

ActiveCell.Offset(, 6).Value = "Yes"

ElseIf OptionButton4.Value = True Then

ActiveCell.Offset(, 6).Value = "No"

End If

Here, we have an If … ElseIf Statement. We're testing OptionButton3 and OptionButton4 for a value of True. Our OptionButton3 is the Yes option, while OptionButton4 is the No option. Depending on which option button was selected, we put either Yes or No in the ActiveCell that has an Offset column of 6.

There's only one more thing left to do now and that's copy the image over to the images folder. We'll do that in the next section below.

<< Excel VBA Course Menu

Lots more free online courses here on our main Home and Learn site

© All course material copyright Ken Carney