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.

 

[fdgsogc]

Nope. No luck.

I should have admitted this earlier. I am using Excel 97. Does that have any implications?

I am getting the error: Sort method of Range class failed.

 

[firefytr]

You might be dealing with an order of operation here. Try altering these two lines...

    With Sheet1.Range("B5:H" & Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row)
        Select Case Me.ComboBox1.Value

This way you're using the combobox value instead of the worksheet linked cell. I don't know of any issues from 97, it should support ActiveX controls.

Does that help?

Regards,
Zack Barresse

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

 

[fdgsogc]

Thanks for the code Zack. I added it to the macro. This code has no issues. But I still have the issue with the sort code. I'm not having an issue with the CASE statement. When I step through the macro, it is taking the value from the combobox and finding the right CASE statement to execute. It bombs when it hits the sort code.

: .Sort Key1:=.Parent.Range("B5").Value, Order1:=xlAscending, Header:=xlYes

When I used the combobox from the Forms Toolbar I didn't have this issue. The only reason I'm now using the combobox from ControlToolbar is because of the order of execution issue I was having.

The error message I get when executing the sort code above is:

Sort method of Range class failed.

 

[DirkStruan]

FDGSOGC

I tried your workbook & code.

Given the sort is only performed on the second change of the drop down, there looked to be something funny with the screenupdating.

If you remove both references to the screenupdating, it works perfectly (for me).

If you leave both in, the screen will also update correctly if you perform another action like triggering a msgbox.

Dirk

 

[SkipVought]

If your Table Column Headings are identical to the Combobox Selections, then you do not need a Select Case...

Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    With Sheet1.Range("BillingData")
        .Sort Key1:=.Parent.Cells.Find(Sheet1.Range("Selection")), Order1:=xlAscending, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

BTW the Sort Key is NOT a Value, in this case it's a RANGE

Key1 Optional Variant. The first sort field, as either text (a PivotTable field or range name) or a Range object ("Dept" or Cells(1, 1), for example).

Skip,

_[red][/red]

 

[SkipVought]

sorry, you'll also need a function to return a Range Name from a Heading String...

function HeadingRange(sHeading as String) as string
   dim i as integer
   If IsNumeric(Left(sHeading,1)) then _
     HeadingRange = "_"
   for i = 1 to len(sheading)
      select case mid(sheading, i, 1)
         case "0" to "9", "a" to "z", "A" to "Z", "\","?","."
            headingrange = headingrange & mid(sheading, i,1)
         case else
            headingrange = headingrange & "_"
      end select
   next
end function

Skip,

_[red][/red]

 

[fdgsogc]

Skip,

Where do I reference the function HeadingRange in your Sep 02, 2006 9:38 update of the Private Sub ComboBox1_Change()?

 

[SkipVought]

Sorry, I was confused. The FIND looks for a Heading String and NOT a Range name. So the ComboBox1_Change code should execute OK.

Skip,

_[red][/red]

 

[combo]

Hi,
there are some topics in the thread that are separate problems.

The first is that the debug error occurs when the excel file is opened in the browser (in my case IE), also from the local drive. A hidden excel instance is created to handle the file, and it behaves differently from regular file in excel. In normal mode, the active workbook has window (Application.Windows.Count returns 1), in debug mode the same in immediate window returns 0. And there is no active workbook, worksheet and cell. This may cause other problems in debug mode that do not occur without debugging.

The second: delay in sorting selection. I would extend Dirk's idea and not use the linked cell at all. Instead use ComboBox1 selection directly:

Private Sub ComboBox1_Change()
    Application.ScreenUpdating = False
    With Me
        .Range("BillingData").Sort Key1:=.Range("BillingData").Cells(1, .ComboBox1.ListIndex + 1), Order1:=xlAscending, Header:=xlYes
    End With
    Application.ScreenUpdating = True
End Sub

and consider removing ScreenUpdating settings if no other code in the procedure above is.

combo