Macro error

[fdgsogc]

I am getting the following error Method 'Worksheets' of object '_Global' failed when I step through my macro in Excel. On my local desktop I have no issues. But when I post this Excel file on my website, I get the error.

So the macro is running erratically. Please go to

http://www.digiuseppe.ca/billings,

click Yes to allow macros and then go to the Visual Basic Editor and step through the macro. At the set sortselection line I get the error.

 

[SkipVought]

Hi,

I have no idea why the Sheet Name will not work.

HOWEVER, you ought to use the Sheet CodeName, which does work. That way a user cannot mess up your code by changing the sheet name...

Set sortselection = Sheet1.Range("$M$10")

Skip,

_[red][/red]

 

[fdgsogc]

Thanks SkipVought.

That got me past the set error.

Now I have the same error at this part of the code where the sorting is taking place. Got any ideas here?

Application.Goto Reference:="BillingData"

 

[SkipVought]

Is BillingData a Named Range?

Is it on the ActiveSheet?

If it is a named range on a different sheet...

with range("BillingData")
   .parent.activate
   .select
end with

Skip,

_[red][/red]

 

[firefytr]

Is there a reason you need to GoTo that range? You know that you can manipulate it wtihout actually selecting/activating it, right? Plus I think it's a very bad habit and poor coding quality.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

I agree with Zach.

Check out...

How Can I Make My Code Run Faster? faq707-4105

Skip,

_[red][/red]

 

[fdgsogc]

Thanks guys. I agree with you 100%. I am an Excel superuser who records macros and then manually adjust some aspects of the code. I'm not a real programmer.

I looked at your FAQ and I conceptually understand it. I just don't know the proper VBA code to make my sorting of the named range BillingData work like the FAQ.

To answer Skip's questions, BillingData is a named range on the active worksheet.

Can you guys suggest the coding to emulate the standards in the FAQ for the code below?

    Application.Goto Reference:="BillingData"
    Selection.Sort Key1:=Sheet1.Range("b5"), Order1:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

 

[firefytr]

Then you might try ...

    With Sheet1.Range("BillingData")
        .Sort Key1:=.Range("B5").Value, Order1:=xlAscending, Header:=xlYes
    End With

See if that helps.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[SkipVought]

    With Range("BillingData")
        .Sort _
            Key1:=.Parent.Range("b5"), Order1:=xlAscending, _
            Header:=xlYes, _
            OrderCustom:=1, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom
    End With

Skip,

_[red][/red]

 

[fdgsogc]

Thanks for your help. I tried both your code suggestions and they worked (had to add .Parent to firefytr's code). I am no longer getting the Method 'Worksheets' of object '_Global' failed when I step through my macro. However, I still have the original problem of the macro not sorting properly. You will notice that my cell link (M10) for the drop down box is recording the correct value. And that when you step through my macro you can see that the correct IF statement is being processed, but for some reason, it is not sorting properly.

Any ideas?

Worksheet can be found at

http://www.digiuseppe.ca/billings.

 

[SkipVought]

How is M10 related to your sorted table? Your SORT macro references B5???

Skip,

_[red][/red]

 

[fdgsogc]

M10 contains the value from the list. So if someone chooses to sort by Company, then M10 will equal one (1). Then when the macro goes through my IF statement, it will run the code where M10 or "sortselection" is equal to one.

B5 is the reference for the first condition where sortselection equals one. You will notice that for each additional if condition, the cell reference to sort on changes to the next column.

This macro works fine on my desktop. Save it to your desktop and try it. The problem is that for some reason, when running it on the website, it is not sorting properly. I believe there is a cell reference issue, but I'm not getting any error message to help me determine why.

 

[SkipVought]

"You will notice..."

there's a Username & Password required to view your example.

Skip,

_[red][/red]

 

[fdgsogc]

I'm so sorry. I have removed the security on that folder now. Thanks for sticking this out with me.

 

[firefytr]

I'm not sure what the problem is, this works for me..

Sub SortBillings()
    Application.ScreenUpdating = False
    With Sheet1.Range("BillingData")
        Select Case Sheet1.Range("M10")
        Case 1: .Sort Key1:=.Parent.Range("B5").Value, Order1:=xlAscending, Header:=xlYes
        Case 2: .Sort Key1:=.Parent.Range("C5").Value, Order1:=xlAscending, Header:=xlYes
        Case 3: .Sort Key1:=.Parent.Range("D5").Value, Order1:=xlAscending, Header:=xlYes
        Case 4: .Sort Key1:=.Parent.Range("E5").Value, Order1:=xlAscending, Header:=xlYes
        Case 5: .Sort Key1:=.Parent.Range("F5").Value, Order1:=xlAscending, Header:=xlYes
        Case 6: .Sort Key1:=.Parent.Range("G5").Value, Order1:=xlAscending, Header:=xlYes
        Case 7: .Sort Key1:=.Parent.Range("H5").Value, Order1:=xlAscending, Header:=xlYes
        End Select
    End With
    Application.ScreenUpdating = True
End Sub

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fdgsogc]

Gentlemen,

I have figured out what the problem is. I still don't know how to fix it.

PROBLEM:
When the Excel file is being executed from the website, for some reason, it is executing the macro before updating the Cell Link of the drop down field.

If I select Sort by Company. Nothing sorts. But the cell link updates to 1. Then select sort by Domain. It will sort by Company and then update the cell link to 2.

This does not occur when the file is run from the desktop.

Any ideas.

 

[firefytr]

Does it work this way for all others? What you're saying is it's offset by one?

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fdgsogc]

No. It's not offset by one. It's using the value of the cell link before the drop down box updates the cell link to its new value.

So if I select Sort By Owner. That new cell link value is equal to 5. However, the previous cell link value was, say, 1 because I previously had selected Sort By Company.

Here's how you can verify this. Open the Excel file at

http://www.digiuseppe.ca/billings.

Select Sort by Owner. Notice that the cell N10 is now equal to 5. Notice that nothing sorted. Then select Sort By Company. Notice that it is now sorted by Owner and the cell link at N10 is equal to 1.

The order of execution is:
1. Run macro.
2. Update cell link N10.

When you save this Excel file to your desktop and run it, the execution order is the opposite.

 

[fdgsogc]

OK. I was using the Combobox from the Forms Toolbar. I have switched to the Combobox from the ControlBox Toolbar in hopes that I won't have the execution order issue I mentioned above.

However, the macro is no longer working. Not even at the desktop level. If you guys are still with me, can you take a look, please?

 

[firefytr]

By taking out module 1 and 2 and using this code, it works great for me..

Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    With Sheet1.Range("BillingData")
        Select Case Sheet1.Range("Selection")
        Case "Sort by Company": .Sort Key1:=.Parent.Range("B5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Domain Name": .Sort Key1:=.Parent.Range("C5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Start Date": .Sort Key1:=.Parent.Range("D5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Last Billed To Date": .Sort Key1:=.Parent.Range("E5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Owner": .Sort Key1:=.Parent.Range("F5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Unused1": .Sort Key1:=.Parent.Range("G5").Value, Order1:=xlAscending, Header:=xlYes
        Case "Sort by Unused2": .Sort Key1:=.Parent.Range("H5").Value, Order1:=xlAscending, Header:=xlYes
        End Select
    End With
    Application.ScreenUpdating = True
End Sub

I'm not sure what else we're doing differently..

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci