Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Worksheet before close (Excel 2000)

    I need to reset certain columns so that our Nitpick 5000 system doesn't choke on an incorrect column width. I have the settings in a recorded macro, but I am not sure how to get them to apply to ALL sheets in the workbook in the easiest way. Do I have to assign a variable the number of sheet and then do a loop through...how should I set that up to cover all the sheets? TYIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Worksheet before close (Excel 2000)

    Double click the ThisWorkbook node in the Project Explorer on the left hand side of the Visual Basic Editor.
    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim wsh As Worksheet
    For Each wsh In Me.Worksheets
    wsh.Range("H:H").ColumnWidth = 12
    Next wsh
    End Sub

    Change H to the column you want to modify, and replace 12 with the desired column width (as a number of characters). Add lines for other columns as needed. The Workbook_BeforeClose event will occur before the workbook is closed.

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Worksheet before close (Excel 2000)

    Thanks! Just what I needed. Help me learn a little here...when you write For Each wsh in me.worksheets....what is me. alluding to? Do I need to change it?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Worksheet before close (Excel 2000)

    The ThisWorkbook object is the workbook as a whole. In the module belonging to this object, Me refers to ThisWorkbook. So (in this module alone), the following are equivalent:

    Me.Worksheets
    ThisWorkbook.Worksheets
    ActiveWorkbook.Worksheets (if the workbook is the active one)
    Workbooks("MyWorkbook.xls").Worksheets (where MyWorkbook.xls is the name of the workbook)

    Similarly, in the module belonging to a worksheet, Me refers to that worksheet. So in the module of a worksheet, the following are equivalent:

    Me.Range("A1")
    ActiveSheet.Range("A1") (if Sheet1 is the active sheet)
    Worksheets("Sheet1").Range("A1") (where Sheet1 is the name of the worksheet)

Posting Permissions

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