Macro is not removing cells correctly from a form control button

[shyy]

Hey everyone,

I have this macro where if I run it, it will work. However when I run it from my form, it doesn't work. What I want to do is select the active row and delete cells that don't have a formula.

When I run the macro within my form, it deletes all the cells, but when I run the macro without opening the form, it seems to always work. Anyone know why? Thanks in advance

    Range("D" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
    Selection.SpecialCells(xlCellTypeConstants).Select
    Selection.ClearContents

 

[shg]

All controls on a form are ActiveX.

As I said, I have tried it with ActiveX controls both on a sheet and a form, and it works fine. If it still doesn't for you, put a stripped-down example on box.net and post a link.

 

[shyy]

Here is the link to box.net thanks for this site

http://www.box.net/shared/jobcap8gdj

The name of the macro to use is macro1 and not the one in the file, sorry forgot to modify that.

 

[mikerickson]

Instead of Rows(ActiveCell.Row) try using ActiveCell.EntireRow.

I note that all the code posted used unqualified references. (Also, does your form activate a different sheet when you open it.)

 

[shg]

I'm unable to download your file from box. is it password-protected?

 

[shyy]

There shouldn't be any password protection, try changing the extension to .XLSM. Its a 2007 format, but I changed it to .XLS since that still works.

I will try the ActiveCell.EntireRow thanks

 

[shyy]

I tried this and it still would delete everything in the row. This form picks up the firs tab, which later will be given a name (something "underlying"

What do you mean by unqualified references?

Sub removecells()
   ' Range("D" & ActiveCell.Row & ":Z" & ActiveCell.Row).Select
   ' Selection.SpecialCells(xlCellTypeConstants).Select
   ' Selection.ClearContents
On Error Resume Next
ActiveCell.EntireRow.Range("D1:Z1").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

Instead of Rows(ActiveCell.Row) try using ActiveCell.EntireRow.

I note that all the code posted used unqualified references. (Also, does your form activate a different sheet when you open it.)

 

[shyy]

Would you happen to have Office 2007? If not I can recreate this in 2003.

I'm unable to download your file from box. is it password-protected?

 

[shg]

There shouldn't be any password protection, try try changing the extension ...

I can't download your file at all.

 

[shyy]

Try this link I converted it into a 2003 file.

http://www.box.net/shared/jobcap8gdj

Just previewed it, amazing upload site!

 

[shg]

The button works fine for me. You are aware that it clears the constants in the row of the active cell?