Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Feb 2003
    Location
    Bournemouth, Dorset, England
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Identifying Sheets in VB (xl97)

    Does anyone know the code to being able to set a sheet name (that can change at any time) as a variable. The sheet name may change, but I need it to say for instance, when a user enters data on sheet 5, the macro recognises that sheet 5 has an entry made, and performs the macro action on that sheet. When a user makes an entry on sheet 3, the macro runs again on sheet 3. If sheets 5 and 3 are then renamed, the macro is still able to run when entries are made on these sheets.
    Does that make sense?

    Thanks.

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

    Re: Identifying Sheets in VB (xl97)

    You can use the SheetChange event of the Workbook object for this. This event occurs whenever the user changes a cell in any worksheet.

    To create code for this event, do the following:
    <UL><LI>Activate the Visual Basic Editor (Alt+F11).
    <LI>Activate the Project Explorer (Ctrl+R).
    <LI>Expand your workbook.
    <LI>Double click ThisWorkbook.
    <LI>Select Workbook from the Object dropdown list.
    <LI>Select SheetChange from the Procedure dropdown list.[/list]The first line of the event procedure looks like this:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    sh is an object that represents the worksheet in which the change occurred, and Target is an object that represents the cell(s) in which the change occurred.

    Here is a very simple example. It displays the sheet name and cell address of the changed cell(s) in the status bar:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.StatusBar = Sh.Name & ": " & Target.Address
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Identifying Sheets in VB (xl97)

    In addition to Hans' comments:
    If you want code to be done only on a particular sheet you can put the code in the sheet object instead of the thisworkbook object:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'code here
    End Sub

    and the code will run even if the sheet name changes. You can add this type to multiple sheets

    If you want to use the Workbook_SheetChange you can use an IF to compare to the sheetname

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    if sh.name = "Sheet 1" then
    'code here
    end if
    End Sub

    But that will fail if they change the sheet name. To get around this, you can create a name that refers to a cell on the sheet of interest
    Insert - name - define
    "SheetReference1"
    Refers to:
    =Sheet1!$A$1

    Then use instead something like:
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    if sh.name = range("SheetReference1").parent.name then
    'code here
    end if
    End Sub

    And this will always refer to that sheet even if the sheet name changes, since the name will be updated (by excel) if/when the sheet name changes.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Paragould, Arkansas, USA
    Posts
    194
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Identifying Sheets in VB (xl97)

    Steve,
    As usual, there's more than one way to skin an Excel cat. Besides a Name, each sheet has an index. The first sheet is 1, then 2, etc. Use the index, and as long as the sheets aren't re-ordered, the name doesn't matter. Choose your poison.

    In Hans' SheetChange example, the index is in Sh.index.

    Errol

Posting Permissions

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