Worksheet Activation and If Then Errors

Hello,

I am brand new (Day 1) to VBA programming and I am running into an issue when trying to activate a sheet based on the value chosen in my userForm that I cannot seem to solve. The error seems to be a compile error and says I am missing and End statement to one of my If Then statements, specifically:

Compile error:
End If without block If

I thought if the If Then statement was all on one line that I would not need an End If statement. In fact, if I try to add an End If statement, I get an error (all proceeding text turns red).

This is the part of the code with the End If error. Not sure what I am missing here.

Please Login or Register  to view this content.

Thanks for the help!
Michelle

Sktneer,

So, you just took the End If off the first If statement to transfer information? When I do that I get a Run-time error '424': Object Required.

Thanks for your time,
Michelle

Norie,

When I try that I get a Compile error: Variable not defined popup which then highlights "Monthly_SWA_Data" i the first If statement to activate a sheet. I have verified that the spelling of the worksheet name is exactly as it appears in the code.

Thanks,
Michelle

Norie,

MonthlySWATrainingButton, MonthlyHSWATrainingButton are Option Buttons on my UserForm and Monthly_SWA_Data, Monthly_HSWA_Data, etc are the names of the worksheets which correspond to the buttons of a similar name.

I do not have anything in my code for the Option Buttons except to set the MonthlySWATrainingButton.Value as the default (True) and in the sheet activation code I posted. Do I need to have separate code for this button if all I want it to do is trigger another action (like setting the active worksheet if chosen)?

Let me step back and state what I have and what I am trying to do as a big picture. The goal is to have a user form which allows data to be entered by anyone and then when the Add Data button is chosen, allocates the data to the appropriate worksheets based on the specific user inputs. Ultimately I will also add a dashboard which plots and reports this data but I am not even close to that step yet.
I have a user form which contains:
1. A comboBox that allows me to choose a person's name from the dropdown box.
2. A Training type group/frame where I have 3 Option Buttons which will determine what worksheet the data will be appended to
3. A comboBox with a dropdown of all the POST Training course names. My plan is to have this grayed out unless the POST Training Option button is chosen in #2 but I have not added this functionality yet.
4. A textBox where the duration of the course in hours can be input
5. DatePicker which when the dropdown arrow is clicked brings up a calendar to choose a specific date
6. Add Data button to be used to append the new data into the appropriate fields/worksheet
7. Clear Data button to be used to clear all the fields in the user form
8. Close form button to close the form

The userform is activated by a button in the spreadsheet which when clicked opens up the userform (i.e. calls UserForm_Initialize()). This seems to work. It is just when I go to enter in new data using the Add Data button where I run into errors. There are additional worksheets in the same workbook with names corresponding to the sheets I am trying to activate (dependent on the Option button selected).

So far all the code I have for the UserForm is:

Please Login or Register  to view this content.

Thanks for your help,
Michelle

Norie,

Ok, here you go. It took a second to make a generic copy.

Thanks again for your help.

Oh, that makes sense. I added Worksheets("...").Activate to the 3 if statements at the top but still getting the Compile error: End If without block If error.

Originally Posted by Norie

Did you try the code I just posted?

Yes, it was the response right before this posting. I added the Worksheets("worksheetname").Activate to the three If Then statements but I still get a compile error:
Compile error: End If without block If error.

Originally Posted by Norie

Did you try the code I just posted?

Hello Norie,

It looks like I missed the nuance of placing Cells(emptyRow, 1).Value = DurationTextBox.Value onto the next line hence giving the End If error.

Thank you so much for your help. I am sure I will be back with more questions.
Michelle

Originally Posted by Norie

You shouldn't need to add to or change anything in the code I posted in post #10, it works without error in your workbook.

You are absolutely right. I got the error when I entered the changes in myself instead of copying and pasting what you posted but I realized what I missed from your code. Thank you again.