+ Reply to Thread
Results 1 to 7 of 7

Command Button Clears Information

  1. #1
    Registered User
    Join Date
    07-24-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    3

    Command Button Clears Information

    Whats up guys! I am writing a small program that takes user inputs from a Userform, does some algebra with them, then spits out the data to a worksheet. I have a button (BACK) on the worksheet that is supposed to bring up the userform again in case a mistake was made and the user can go back and change an input. The problem is, after the numbers are changed and the user clicks the 'RUN' button again, it clears all information from the worksheet. The code works like this:

    1. User enters information into userform. When he/she is done they click the 'RUN' button
    2. The RUN button is a command button with code written that says to take the information and write them to cells in a worksheet.
    3. In this worksheet I use excel's math functions to perform calculations.
    4. Within the same code in the RUN button it then take the results of the calculations and writes them to a separate worksheet that is in a printable format.
    5. The 'BACK' button code is simply "UserForm3.Show".

    When the 'BACK' button is clicked the userform is shown and information can be edited, but when the RUN button is clicked again, all information from the worksheet is deleted. Does anyone have an idea why this is?

    I tried to make this clear as possible but it's tricky to explain. Let me know if you have questions.

  2. #2
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Command Button Clears Information

    You might have to attach a sample workbook with the userform and the code you are using.
    1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG
    You don't have to add Rep if I have helped you out (but it would be nice), but please mark the thread as SOLVED if your issue is resolved.

    Tom

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Command Button Clears Information

    Hi mike428vit

    Welcome to the forum...
    Possibly???
    I assume that because you have a button that calls the userform, you are unloading your userform after running code...
    When you unload your userform all information that previously was captured is cleared and therefore when you press run again...blank info is populated to sheet...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    07-24-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    3

    Re: Command Button Clears Information

    Here's some example code that is written in the RUN button in the program. Anything in italics is commentary to help you guys better understand

    Write data from userform to cells to perform mathematical functions.
    Sheets("Sheet1").Cells(16, 11).Value = UserForm3.TextBox3.Value
    Sheets("Sheet1").Cells(16, 13).Value = UserForm3.TextBox1.Value
    Sheets("Sheet1").Cells(16, 14).Value = UserForm3.TextBox2.Value

    Take data from cells and insert them into different worksheet which is already formatted
    Sheets("Module Alignment").Cells(17, 4) = Cells(24, 12).Value 'Delt H and Delta J 12-3
    Sheets("Module Alignment").Cells(18, 4) = Cells(24, 13).Value ' H9
    Sheets("Module Alignment").Cells(19, 4) = Cells(24, 14).Value ' H6
    Sheets("Module Alignment").Cells(20, 4) = Cells(24, 15).Value ' H3

    Hide this userform and show the worksheet where the information has been sent.
    Worksheets("Module Alignment").Select 'Show worksheet for easier printing
    UserForm3.Hide

    On the work sheet there is a commandbutton labeled BACK. The only code in the command button is:
    UserForm3.Show

    To clarify, the information is still in the userform when the BACK button is clicked. Its when the RUN button is clicked again that all the data is cleared from the worksheet itself.

  5. #5
    Forum Expert gmr4evr1's Avatar
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    Office 2010 and 2007
    Posts
    3,448

    Re: Command Button Clears Information

    Without being able to see how it's all working, I'm guessing here.
    Is there a specific reason that the userform is being hidden instead of unloaded?

  6. #6
    Valued Forum Contributor Maudibe's Avatar
    Join Date
    12-21-2016
    Location
    USA
    MS-Off Ver
    2010-2013
    Posts
    326

    Re: Command Button Clears Information

    Mike,

    I was able to duplicate your issue. The reason you are getting blanks is because when you hit the back button to show the form then click run, the active sheet is not sheet1, it is Module Alignment and cells L24:O24 have nothing in them on the Module Alignment sheet.

    To correct the issue, you must reference the sheet in your code or add code to the back button to select Sheet1

    Method 1: reference the sheets in the code
    Please Login or Register  to view this content.
    Method 2: Make Sheet1 the active sheet (Back button) when rerunning you userform code
    Please Login or Register  to view this content.
    HTH,
    Maud

  7. #7
    Registered User
    Join Date
    07-24-2017
    Location
    Virginia
    MS-Off Ver
    2010
    Posts
    3

    Re: Command Button Clears Information

    I used the second method out of simplicity and it worked perfectly. THANKS!

+ 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. Replies: 1
    Last Post: 04-11-2017, 02:11 PM
  2. Replies: 2
    Last Post: 02-06-2017, 03:43 PM
  3. Excel VBA command button to add or update information
    By VB86 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-23-2014, 11:01 PM
  4. command button that will send information from excel spread sheet to access
    By superchew in forum Access Tables & Databases
    Replies: 5
    Last Post: 12-16-2013, 11:45 PM
  5. Transfer information from one sheet to another using a command button!
    By melliard5779259 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-31-2013, 05:02 AM
  6. Locked textbox clears text when command button clicked.
    By cocobean in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2012, 02:53 AM
  7. Get information from Another Worksheet via Command Button
    By novemberoyce in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-17-2010, 01:20 AM

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