running a function from a form 1

[sedgely]

well, now i'm really confused (doesn't take much LOL).
i have created the following to clear the contents of any unlocked cells on a number of worksheets.

Function clearUserInput()
'Clears any data that has been input by the user

'Application.ScreenUpdating = False
Dim shName
Dim c
Dim wb As Workbook

Set wb = ThisWorkbook
With wb

For Each shName In Array("sheet1", "sheet2", "sheet3", "sheet4")
    For Each c In Range("A1:Gy250")
    If c.Locked = False Then c.ClearContents
    Next
Next
End With
'Application.ScreenUpdating = True
End Function

The code is in a module and runs fine if i run it form there, however i have created a command button on a form to run this. when run from the form it doesn't work - appears to run OK with no errors, but does not clear the cells.
Anyone spot where i am going worng with this?

Cheers, Craig
Si fractum non sit, noli id reficere

 

[PHV]

Perhaps this ,
For Each c In [!]shName.[/!]Range("A1:Gy250")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sedgely]

get "object Required" error with that.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[PHV]

OOps, sorry for the typo:
For Each c In Worksheets(shName).Range("A1:Gy250")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sedgely]

PH you're a genius, have a star. still don't understand why it made the difference but it did.

Cheers, Craig
Si fractum non sit, noli id reficere

 

[stevexff]

It's because shName is a String, not a WorkSheet object. So it doesn't have a Range method...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]