Consulting

Results 1 to 15 of 15

Thread: Solved: Add a Clear Button

  1. #1
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location

    Solved: Add a Clear Button

    I want to add a Clear Button to a "WorksheetForm"so that a user can clear the "WorksheetForm" and start over if they mess up...CAN YOU HELP?

    Best Regards,

    Charlie

  2. #2
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    I've got to stop doing this so late at night.....

    Sub clear_IT()
    '
    ' clear_IT Macro
    ' Macro recorded 12/6/2002 by ...
    '
    '
    Range("E3,E5,W5,E7,S7,L9,M11,E13,E15,R15,A18,A20,A22,A24,A28,J28,L28,N28,P2 8,R28,T28,A34,L30,P30,S30,L31,P31,S31,L32,P32,S32,L33,P33,S33,A34,A38,F38,E 41,E43,E45,K45,A53,A63,A65,A67,A69").Select
    Range("E3").Activate
    Selection.ClearContents
    End Sub

    Regards,

    Charlie

  3. #3
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Your solution above will work, but if you make ANY changes to the layout of your input sheet, your macro will also need to be adjusted, so it's not the ideal method.

    The merged cells in your input sheet are going to create a problem. Off the top of my head I can think of a couple of options:
    • Unmerge your cells. You don't actually have to have them merged in order to have your text span several cells across, and rather than merging vertically you can just increase the height of the row. This would allow you to create a macro that looped through the sheet, and cleared the contents of any cells that have the colour applied.
    • Name the ranges. You could create named ranges "dat1", "dat2" etc. until all the input ranges are named. You need to be careful still with the merged cells; for example, your first range needs to be E3:I3, not just E3. If you do this, you could use the following code to clear them all:
    [vba]
    Sub DataClear()
    Dim i As Integer


    On Error GoTo ExitHere
    Do
    i = i + 1
    Range("dat" & i).ClearContents
    Loop

    ExitHere:
    End Sub
    [/vba]

  4. #4
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thamks for the tip. I'll take a look at itwhen I'm not so tired. How do you name the ranges???? Is it the same as naming the ranges for a "list" not quite sure.

    Regards,

    Charlie

  5. #5
    Moderator VBAX Master geekgirlau's Avatar
    Joined
    Aug 2004
    Location
    Melbourne, Australia
    Posts
    1,464
    Location
    Insert | Name | Define

    You can use the Name box to the left of the formula bar, but it's not going to work correctly with your merged cells.

  6. #6
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thanks for you time on this. How will it work when someone is typing in data into one of the a large cell areas, cell A18 " Describe event* (including date, approximate time, and callsign(s) if applicable". It seems that the typed data would just fill=in ceel A18 and not acrossed?

    When I "Insert | Name | Define" do I put all of the "dat1", "dat2", and so on in a single column as I've done on my work sheet "LookupList" and used on the "Input" worksheet, cell W5????

    Best regards,

    Charlie

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Keep a blank copy of your form as a hidden sheet. Your macro can copy and paste from that to reset to initial values. Renaming can also be considered, but might give problems with linked data.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Good morning Sir. I made a copy of the "Inpput" worksheet form, but after that I'm not following what your saying.....



    Best regards,

    Charlie

  9. #9
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Here's a copy of the workbook. I've made a "name list" for the "dat's", but not sure how to apply what geekgirlau was trying to convey for me to do??? Take a look see.

    Regards

    Charlie

  10. #10
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Here's a Clearit Version using a hidden copy
    BTW, Using "." in file names can make code manipulation awkward. Best avoided.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  11. #11
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Sorry for the delay had to drive to work. I've downloaded your edit version to the workbook but I'm not getting any data to go into the "template"??? Is the idea to input data into the "Input" worksheet and if the Clear button is activated the data goes from the "Input" worksheet to the "Template" worksheet or is it the other way around???

    Also, do you see an issue, like "geekgirlau" does with merged cells?

    Appreciate you time helping out on this.

    Regards,

    Charlie

  12. #12
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I agree with GG about merged cells etc. but basically my solution should clear whatever you have entered. One you have sorted out "Input" to work properly, save a copy of the blank form as "Template" and hide it. Click the ClearForm button to clear the form. If you check the macro, you'll see how I've amended the code.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  13. #13
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    I'll take another look at the macro. How can I have larger cell to input information without merging the cell in the same row??? The cell would have to be able to wrap the text back around again????

    In your opinion, as GG suggested use the dat1, dat2, and dat3, naming each input cell by these "dat", and using GG macro to clear the worksheet form???

    Regards,

    Charlie

  14. #14
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    I confess, I've not looked at the detail of your form; only how to clear it as per your initial request. I'll have a look at your merge cell problem.
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  15. #15
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thank you for time and efforts thus far.

    Regards,

    Charlie

Posting Permissions

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