Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002

    Unanswered: background event in excel


    excel 2k

    I have multiple sheets in my workbook. An event triggered by a button on a userform runs the routine bellow from sheet1 into sheet4, but the only way it runs is if I use 'sheet4.activate' first and then sheet1.activate at the end. is there a way this can be done without having to move from one sheet to the next and without being seen by the user? in another word, I want the whole thing to happen in the background without ever being noticed by anyone.

    Sub eleminate()
    Dim vrange As Variant
    Dim irowcount As Integer
    vrange = Sheet4.Range("A1:J11")
    irowcount = UBound(vrange)
    Dim a As Integer
    For a = 1 To irowcount
    If Sheet4.Range("C" & a).Value = 0 And Sheet4.Range("D" & a).Value = 0 And Sheet4.Range("E" & a).Value = 0 Then
    Sheet4.Range("A" & a & ":J" & a).Select
    Sheet4.Range("A" & a & ":J" & a).Clear
    End If
    Next a

    End Sub

  2. #2
    Join Date
    Feb 2004
    What you have there is pretty close to what you need. If you are referring to a worksheet that is not active you can not 'Select' Objects on that sheet. Remember an object has to be active in order to Select it. Additionally you must qualify your statements with the name of the object, when you are referring to a worksheet that is not active, "Sheet4" in this case, which you have. You will find the "With" Statement invaluable for this purpose. See the online exel vb help for details on use of the 'With' statement. Also see my reply in your other post "moving a row upward in excel" where I used 'With' and 'Cells' to reference non-selected objects.

    This line in your code: "Sheet4.Range("A" & a & ":J" & a).Select"
    Will produce an error if the sheet 'Sheet4' is not active.


Posting Permissions

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