+ Reply to Thread
Results 1 to 2 of 2

Macro activated from multiple sheets causing mixed results

Hybrid View

  1. #1
    Registered User
    Join Date
    10-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Macro activated from multiple sheets causing mixed results

    I have a macro which is being activated from multiple places and am trying to find a way to limit the way that it works, as it is causing unexpected results

    The macro in question is a date stamp- it activates when a checkbox is checked, and places a stamp in the next cell
    Sub Process_CheckBox(pObject)
    
        Dim LRow As Integer
        Dim LRange As String
        
    
        'Find row that checkbox resides in
        LRow = pObject.TopLeftCell.Row
        LRange = "H" & CStr(LRow)
    
        'Change completed date, if checkbox is checked
        If pObject.Value = True Then
            ActiveSheet.Range(LRange).Value = Date
    End With
        'Clear completed date if checkbox is unchecked
        Else
     ActiveSheet.Range(LRange).Value = Null
     
        End If
    
    End Sub
    I have also placed linked checkboxes on several other sheets, so that if a checkbox is checked in one of the sheets, the corresponding box is checked in the other sheets as well, however I only want the date stamp to be issued on the master sheet

    The 2 problems I'm having are:
    When I check a checkbox in one of the "secondary" sheets, it puts the time stamp in that sheet. (I know that's because of me using ActiveSheet in the code, but I've tried to select the master sheet to no avail)
    If i turn off the date stamp, the linked checkboxes work perfectly, but when the date stamp macro is active, and I check a checkbox in one of the secondary sheets, it DOES register the check on the master sheet, but not on the other secondary sheet.

    Any suggestions as to how to tidy this up would be appreciated.

  2. #2
    Registered User
    Join Date
    10-17-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Macro activated from multiple sheets causing mixed results

    Just realised the rest of my query didn't save - sorry!

    I also have some of these date stamp checkboxes linked to other checkboxes in different sheets, so if a particular checkbox is checked in one sheet, the corresponding checkbox is also checked in the other sheets.

    The 2 problems that I'm having are:
    I only want the date stamp (and hence the above macro) to work on one sheet (let's call it the Master). However, when I click on a checkbox in one of the secondary sheets, it tries to place the date stamp there instead and not on the master. I know this is because of me using ActiveSheet in the original macro, but I've tried a few ways of specifying the master sheet, to no avail.

    When I deactivate the date stamp macro, the checkbox linking works perfectly. ie checking a box in any of the 3 sheets will check it off in the other 2. However, once i start trying to use the date stamp again, the cell-linking has issues ie. checking a box in either of the secondary sheets WILL register on the master sheet, but not the other secondary sheet.

    Any help would be greatly appreciated!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1