+ Reply to Thread
Results 1 to 16 of 16

Worksheet Activation and If Then Errors

  1. #1
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    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

  2. #2
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365
    Posts
    9,655

    Re: Worksheet Activation and If Then Errors

    try this.........
    Please Login or Register  to view this content.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  3. #3
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    Perhaps.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  4. #4
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    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

  5. #5
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    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

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    Michelle

    Neither of those errors is to do with the If statements.

    So, there seems like there are other problems with the code.

    What are MonthlySWATrainingButton, MonthlyHSWATrainingButton, Monthly_SWA_Data, Monthly_HSWA_Data etc?

    Also, where is the code located and what is it meant to do?

    PS You'll also get the Object required error with the code I posted if you remove Option Explicit.

  7. #7
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    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

  8. #8
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    Can you upload an example workbook?

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

  9. #9
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    Norie,

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

    Thanks again for your help.
    Attached Files Attached Files

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    The codenames of the worksheets are Sheet1, Sheet2 and Sheet3 not Monthly_SWA_Data, Monthly_HSWA_Data and POST_Data, those are the tab names.

    Try this.
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    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.

  12. #12
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    Did you try the code I just posted?

  13. #13
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    Quote Originally Posted by Norie View Post
    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.

  14. #14
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    Quote Originally Posted by Norie View Post
    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

  15. #15
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Worksheet Activation and If Then Errors

    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.

  16. #16
    Registered User
    Join Date
    04-28-2014
    Location
    Orange County, CA
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activation and If Then Errors

    Quote Originally Posted by Norie View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Worksheet activation after a first calculation cycle has been completed
    By Tempe_VBA in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-15-2014, 01:48 AM
  2. Maximize Worksheet upon activation/Open
    By Williamm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2014, 04:57 PM
  3. Minimise/maximise Modeless Userform on activation/de-activation
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-28-2009, 11:24 PM
  4. Worksheet Activation Weirdness
    By mattevans in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-25-2006, 08:52 PM
  5. [SOLVED] Worksheet Activation from another workbook
    By Ronbo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2005, 05:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1