Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Sundry macro-type questions

  1. #1
    Board Regular ACommandLineKindaGuy's Avatar
    Join Date
    May 2002
    Location
    SF Bay Area
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'd appreciate any help in automating a workbook with a number of sheets where user input data is unlocked/unhidded, and each sheet protected. What I'd like to do is develop macros that:
    1. Delete user input in a particular sheet only by deleting all cells that are unlocked/unhidden by pressing a button in that sheet.
    2. Unprotect all sheets, select all sheets, select all cells of all sheets, right-click copy, and right-click paste special-values so that all sectret-type formulas are replaced by the value they calculate. I can then save the workbook under a new name and distribute it to others.
    3. Select active sheets (what I call active sheets are sheets with a particular named cell >0 (eg SBC!AA6 = SBCCost = $12,430,000)
    and print them.

    Thanks in advance for any consideration of these questions. Back in the days of 1-2-3 v. 2.4 I could do this kind of stuff--I guess this ole dawg isn't up to learning VB--or at least is intimidated by it!


  2. #2
    MrExcel MVP
    Join Date
    Apr 2002
    Location
    Vancouver BC , Canada
    Posts
    6,259
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To get an idea on how various operations occur you might want to record some marco's and then view the code.

  3. #3
    Board Regular ACommandLineKindaGuy's Avatar
    Join Date
    May 2002
    Location
    SF Bay Area
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I agree that recording macros is the best way--but then how do I deal with selecting all unlocked cells in a spreadsheet and clearing their contents?--It seems to me that recording a macro is fine when you know what you want to do in advance of performing the operation. Believe me, I've fiddled all over the place with recorded macros and then trying to change stuff around.

  4. #4
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-12 19:29, ACommandLineKindaGuy wrote:
    I'd appreciate any help in automating a workbook with a number of sheets where user input data is unlocked/unhidded, and each sheet protected. What I'd like to do is develop macros that:
    1. Delete user input in a particular sheet only by deleting all cells that are unlocked/unhidden by pressing a button in that sheet.
    2. Unprotect all sheets, select all sheets, select all cells of all sheets, right-click copy, and right-click paste special-values so that all sectret-type formulas are replaced by the value they calculate. I can then save the workbook under a new name and distribute it to others.
    3. Select active sheets (what I call active sheets are sheets with a particular named cell >0 (eg SBC!AA6 = SBCCost = $12,430,000)
    and print them.

    Thanks in advance for any consideration of these questions. Back in the days of 1-2-3 v. 2.4 I could do this kind of stuff--I guess this ole dawg isn't up to learning VB--or at least is intimidated by it!

    1. Try something like the following (hard code the name of the sheet if needed. You may also need to supply a password with the .unprotect and .protect commands

    Code:
    Sub tester()
    Dim UsedCell As Range
        With ActiveSheet
        .Unprotect
        .UsedRange
            For Each UsedCell In ActiveSheet.UsedRange
                If UsedCell.Locked = False Then
                    UsedCell.ClearContents
                End If
            Next UsedCell
        .Protect
        End With
    End Sub
    2. Do the same as above, but throw it in a loop

    Dim wks as worksheet

    For each wks in thisworkbook.worksheets
    '''your code here
    Next wks

    3. Would be done in your code for #2 above

    If Wks.Range("A1")>0 then wks.printout

    Pseudo code suppliced in 2 and 3 above, so please test some things and try it out.

    Bye,
    Jay

  5. #5
    Board Regular ACommandLineKindaGuy's Avatar
    Join Date
    May 2002
    Location
    SF Bay Area
    Posts
    281
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay, I'll try that

  6. #6
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not to argue with an MVP (especially since I have no access to Excel in this hotel)

    Woudl something like this work?



    ActiveSheet.UsedRange.Select
    Selection.Locked = False
    Selection.ClearContents


    This would be faster than Jay's loop. (this is neither here nor there, because Jay's code should work)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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