Hi, I am using a Userform in exc

[Clarkie001]

Hi,

I am using a Userform in excel to run the following macro.
This works fine, however the issue arises when I make this available on our website for other users to view.

At that point I am getting a Run-Time error 1004 Method 'Sheets'of object'_Global' failed.

I dont understand why it works fine through excel locally but when published through the web site stops.

Any help would be much appreciated.

Clarkie


Sub Usage_Start()

Sheets("Create_Usage&quot.Select
Range("A2&quot.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B2&quot.Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("B1&quot.Select

End Sub

 

[xlbo]

Presumably, it is being opened thru IE - means that certain functionality needs slightly different syntax

Try this for a start but if it doesn't work, post back with the line it bombs on...
Sub Usage_Start()
dim lRow as long
With Sheets("Create_Usage&quot
.range("A2:A" & .range("A2&quot.end(xldown).row).clearcontents
.range(cells(2,2),cells(range("B2&quot.end(xldown).row,range("B2&quot.end(xltoright).column)).clearcontents
end with
End Sub

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[TheBitDoctor]

Clarkie001:

I've had some problems where the system was very finiky about resolving default references. You could also try pre-qualifying the sheets object reference with the workbook...

ThisWorkbook.Sheets("Create_Usage&quot

Ron

 

[Clarkie001]

Hi Ron,

Pre qualifing the sheets gets me passed that first line, now it is falling down at Range("A2&quot.select

Any ideas...


Clarkie

 

[xlbo]

Qualify ALL lines
ThisWorkbook.Sheets("Create_Usage&quot.range("A1&quot.select


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Clarkie001]

Hi xlbo,

Tried that, getting same error number, but different description - "Select Method of Range class failed".

Is there a way to qualify the range select part itself?

Clarkie

 

[xlbo]

Have you tried my initial post ?? (Before we go down this particular line)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Clarkie001]

Yes, I got the original message at the with sheets part.

Clarkie

 

[TheBitDoctor]

Clarkie:

I don't understand why you can reference the sheet but not a range on the sheet!?! Like I said, finicky.

What happens when you try to do something other than select? Place these statements in your code after the sheet selection statement and watch what happens when you run it locally and from the web. Do you get the expected results?

X = ThisWorkbook.Sheets("Create_Usage&quot.range("A2&quot

ThisWorkbook.Sheets("Create_Usage&quot.range("A2&quot = "123"

Y = ThisWorkbook.Sheets("Create_Usage&quot.range("A2&quot.Address

Z = ActiveCell.Address

TheBook = Excel.Application.ActiveWorkbook.Name

TheSheet = Excel.Application.ActiveSheet.Name

WhereAmI = Excel.Application.Selection.Address

Ron

 

[Clarkie001]

Hi Ron,

With that, it places the value 123 in cell "A2".

 

[Clarkie001]

I have just noticed another thing, if I bring up the Macro menu using Alt+F8 and run the macros from there they run fine, but as soon as I try running them from the userform again I get the Run time error message.

Clarkie

 

[Clarkie001]

Hi,

I have been able to resolve this issue by hiding the userform before the codes runs.

Thanks for all your help anyway, my fault it was just a stupid thing (always the worst).

Clarkie

 

[xlbo]

Presumably, this is run from a button on the form ???
If this is the case, check the TakeFocusOnClick property and change it to FALSE if it is TRUE

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244