Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Set default value to cells on worksheet open (2003)

    Want to set range of cells ("c15-C23") =0 on worksheet open. User then inputs into those cells to build data.
    Code below is start of code that updates data after eacn "C" cell is entered.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Refresh: 'Clear all unused cells
    Application.EnableEvents = True
    Range("g1:g43").Interior.ColorIndex = 0
    Range("g1:g43") = ""
    PDU:
    If Range("c15") = 0 Then GoTo Pilot
    Range("G" & Range("l2") & ":G" & Range("l3")).Interior.ColorIndex = 10
    Range("G" & Range("l2") & ":G" & Range("l3")) = "PDU"
    Range("G" & Range("l4") & ":G" & Range("l5")).Interior.ColorIndex = 15
    Range("G" & Range("l4") & ":G" & Range("l5")) = "Reserved Cable Space"
    Pilot:
    If Range("c18") = 0 Then GoTo Slammer
    Range("G" & Range("m2") & ":G" & Range("m3")).Interior.ColorIndex = 20
    Range("G" & Range("m2") & ":G" & Range("m3")) = "PILOT"
    Slammer:
    If Range("c20") = 0 Then GoTo Bricks
    .
    .
    End Sub

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

    Re: Set default value to cells on worksheet open (2003)

    You can use the Workbook_Open event procedure in the ThisWorkbook module to do something when the workbook is opened.

    BTW, I'd use the Worksheet_Change event instead of the Worksheet_SelectionChange event. Worksheet_SelectionChange occurs each time the user selects another cell, whether the previous cell has been edited or not. The Worksheet_Change event occurs when the user has modified the value of a cell.
    Moreover, I would check at the beginning of the code for Worksheet_Change whether the modified cell is in the range C15:C23:

    If Intersect(Range("C15:C23"), Target) Is Nothing Then
    Exit Sub
    End If

  3. #3
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set default value to cells on worksheet open (2003)

    Sorry, not familiar enough with VB in Excel to get around easily. Where is the ThisWorkbook module? Is Workbook_Open event procedure part of it?

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

    Re: Set default value to cells on worksheet open (2003)

    You'll find the ThisWorkbook module in the Project Explorer on the left hand side of the Visual Basic Editor. Double-click ThisWorkbook to open the module.

    You can create a Workbook_Open event procedure in this module by selecting Workbook from the Object dropdown list at the top of the module window, just like you create a Worksheet_SelectionChange event procedure by selecting Worksheet from the Object dropdown list at the top of the code module for a worksheet.
    Attached Images Attached Images
    • File Type: png x.png (4.4 KB, 0 views)

  5. #5
    Star Lounger
    Join Date
    Feb 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set default value to cells on worksheet open (2003)

    Got it, Thank you.

Posting Permissions

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