Workbook Open Event

[pcdaugs]

Hello All,

I have an excel workbook that is being used to track our sales people's call results. Basically, I have a list of prospects that they can call and then choose from a drop down menu whether they left a voice mail, set an appointment, not interested, and a few more options.

Anyway, the sales people want call their prospect 3 time and if they don't get a hold of them they want to recycle (call the lead back) after 90 days. I have added an option in the drop down menu called 'last voice mail' this allows me to know when they left the last message before they are going to recycle them in 90 days. I have setup a worksheet change event that outputs the date they select last voice mail option.

Then I wrote the workbook open event code below that cycles through each of the sales people's sheets and checks the dates. If the date is 90 days old it will highlight the prospect so the sales person knows to start calling them again.

Private Sub Workbook_Open()

Dim stPartner   As String 'Used to pass the value of a cell in Named Range "Partners"
Dim rPartner    As Range 'Used to  loop through each worksheet
Dim rLoop1      As Range 'Used to loop through a "Partner's" worksheet
Dim stSelect    As String
     
    For Each rPartner In Range("Partners") ' Loops through each "Partner's" worksheet
        Select Case rPartner
            Case "Lynn Devitt"
                stPartner = "Lynn"
            Case "Marlys Fiterman"
                stPartner = "Marlys"
            Case "Leslie Martens"
                stPartner = "Leslie"
            Case "Mike Westling"
                stPartner = "Mike"
            Case "John Nentwig"
                stPartner = "John"
            Case "Norv Henrichs"
                stPartner = "Norv"
        End Select
         
        For Each rLoop1 In Worksheets(stPartner).UsedRange.Columns(23).Offset(1).Cells 'Loops through
    
            If rLoop1.Value <> "" And rLoop1.Value <= Date - 90 Then
                stSelect = rLoop1.Offset(0, -22).Address + ":" + rLoop1.Offset(0, 16).Address
                Range(stSelect).FormatConditions.Delete
                Range(stSelect).Interior.ColorIndex = 6
            End If
            
        Next rLoop1
    Next rPartner
End Sub

This code doesn't work when I make the workbook shared so more then one sales person can be in it at the same time. Anyway, the error I am getting is "Run-time Error 1004 application-defined or object-defined error." Any help you can provide as to how to fix this would be much appreciated.

Thanks,

pcdaugs

 

[SkipVought]

the error I am getting is "Run-time Error 1004 application-defined or object-defined error."

on what statement?

Your Range has no Sheet Object. I'm guessing that's the statement(s).

        For Each rLoop1 In Worksheets(stPartner).UsedRange.Columns(23).Offset(1).Cells 'Loops through
    
            If rLoop1.Value <> "" And rLoop1.Value <= Date - 90 Then
                stSelect = rLoop1.Offset(0, -22).Address + ":" + rLoop1.Offset(0, 16).Address
                [b]Worksheets(stPartner).[/b]Range(stSelect).FormatConditions.Delete
                [b]Worksheets(stPartner).[/b]Range(stSelect).Interior.ColorIndex = 6
            End If
            
        Next rLoop1

or better yet, loop1 has an inherited sheet object, so I think this will also work...

  dim rng as range
        For Each rLoop1 In Worksheets(stPartner).UsedRange.Columns(23).Offset(1).Cells 'Loops through
    
            If rLoop1.Value <> "" And rLoop1.Value <= Date - 90 Then
                set rng = range(rLoop1.Offset(0, -22), rLoop1.Offset(0, 16))
                rng.FormatConditions.Delete
                rng.Interior.ColorIndex = 6
            End If
            
        Next rLoop1

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pcdaugs]

Hello Skip,

Thanks for taking a look at this for me. I tried what you suggested with the range object instead of the string. It still doesn't work as a shared document. I also tired the Worksheets(stPartner) and that still gives the same error.

I don't know if I was clear enough earlier but I want you to know that the marco runs based on dynamic named ranges for each of the partner's names. Second, I only get this error when I make the file a "Shared" document. Any suggestions?

Thanks,

pcdaugs

 

[SkipVought]

So exactly which statement are you getting the error on?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pcdaugs]

Hello Skip,

That is the thing, I can't tell where the error is and on which line because when you have the document set to "Shared" you are unable to get access to the VB Editor! It really sucks and I can't figure out how to see where the error is happening. Any suggestions on how I can view the the macro when in "Shared" mode?

Thanks,

pcdaugs

 

[SkipVought]

Let's take this discussion to Forum707 where it would be more appropriate.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops. Were am I???

Must be Friday.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Bong]

The FormatConditions collection Delete method takes a (blank) argument list: Range(stSelect).FormatConditions.Delete[red]()[/red]

_________________
Bob Rashkin