Excel 2010 "ActiveSheet" issue

[Sue-W]

Hi,
I'm relatively new to Excel macros, and have been developing a test procedure for our manufacturing folks to use when testing new equipment. They are using a Panasonic Toughbook tablet PC. I have developed a worksheet which contains the steps for the test procedure. After filling in all the info. and checking the checkboxes as each step passes testing, I have a button for them to click which renames the current worksheet with the test id and saves the workbook. The next time they want to test, they open the workbook, and click another button which copies the worksheet and renames the new worksheet "NewTest", clears out the checkboxes and data fields, and resets the date and time to current. We are using Excel 2010 on both my development computer and the Toughbook tablet.
I have everything working fine on my computer, but the Toughbook tablet will not execute any statement containing a reference to the ActiveSheet or Worksheet. Here's my code:

Sub Copy_Sheet()
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = "NewTest"
Call CheckBoxesFalse
Call ClearContents
Call DatePicker
End Sub

Sub CheckBoxesFalse()
Dim objsheet As Worksheet
Dim objShapes As Shape
Set objsheet = ActiveSheet
For Each objShapes In objsheet.Shapes
If objShapes.Type = msoFormControl Then
If objShapes.FormControlType = xlCheckBox Then
objShapes.ControlFormat = False
End If
End If
Next objShapes
End Sub

Sub ClearContents()
ActiveSheet.Range("b3,c5,f5,c7,f7,c9,f9,b11,c63,b65,b66,b68").Select
Selection.ClearContents
End Sub

Sub DatePicker()
ActiveSheet.DTPicker21.Value = Date
ActiveSheet.DTPicker22.Value = Date
ActiveSheet.DTPicker23.Value = Time
ActiveSheet.DTPicker24.Value = Time
ActiveSheet.DTPicker25.Value = Date
End Sub

I have put alot of time into reading the forums and looking for answers, but no luck so far. I would greatly
appreciate any help on this. I have a feeling it's something really simple, I just don't know enough about
it all to see it.

Thanks,
Sue

 

[wigi]

If you change:

Sub ClearContents()
ActiveSheet.Range("b3,c5,f5,c7,f7,c9,f9,b11,c63,b65,b66,b68").Select
Selection.ClearContents
End Sub

to

Sub ClearContents()
  Range("b3,c5,f5,c7,f7,c9,f9,b11,c63,b65,b66,b68").ClearContents
End Sub

Does that run fine?

 

[Sue-W]

Hi,
When I make that change, I get "Run-time error '1004'" on both computers.
Sue

 

[Sue-W]

I forgot to mention that my development computer is running Windows7 Professional SP1, and the Tablet PC
is running WindowsXP Professional SP3.

 

[daverunt]

Try using the sheet name instead of ActiveSheet as that's the one you are dealing with anyway.

Sheets("NewTest").Range("b3,c5,f5,c7,f7,c9,f9,b11,c63,b65,b66,b68").[COLOR=#333333]ClearContents

[/COLOR]

 

[Sue-W]

Thanks, but the sheet name will be changed to the test ID after the test
is completed, so it will be different each time.

 

[wigi]

- Avoid the use of procedure names like ClearContents: this is also the name of a method in VBA
- Avoid the use of Select and Selection if not absolutely needed
- Are you sure the Datepicker control isn't the problem on the other PC's?

 

[wigi]

Try this - untested - code:

Sub Copy_Sheet()
    Dim ws As Worksheet
    ActiveSheet.Copy After:=ActiveSheet
    Set ws = ActiveSheet
    ws.Name = "NewTest"
    ws.CheckBoxes.ControlFormat = False
    ws.Range("b3,c5,f5,c7,f7,c9,f9,b11,c63,b65,b66,b68").ClearContents
    ws.DTPicker21.Value = Date
    ws.DTPicker22.Value = Date
    ws.DTPicker23.Value = Time
    ws.DTPicker24.Value = Time
    ws.DTPicker25.Value = Date
End Sub

 

[Sue-W]

Thanks for the reply. I'm thinking after doing more digging today that the DatePicker control may be the problem. The guys in Manufacturing are using a semi-functional version of the test procedure right now, but when I get it back I'm going to try adding the control and registering it again. I'll also try your code example above. thanks so much for your help. I'll reply with an update on whether it worked or not tomorrow.
Sue

 

[Sue-W]

Hi,
I tried something I should have done awhile ago, by removing the drop down calendar and clock from
my spreadsheet, everything else worked fine. I guess I got sidetracked reading about issues between
Excel 2003 and 2010, and thought I had to upgrade to solve the issue. I did try to register the DTPicker
control again, and received a message that it was successful, so I'm still not sure how to fix that part,
but at least everything else works fine. If anyone has any experience with problems with DTPicker and
Windows XP Professional, I'd be interested to hear about any fixes. Otherwise, thanks to all for your help.
Sue