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

 

[GlennUK]

What else are you doing in your form code? Is this action done after you hide the form, or is the focus still on the form when you try to run this code?

 

[shyy]

The action is done while the form is still opened. I want the user to input the data in the form and I need a button to clear all the cells that don't have formulas.

 

[shyy]

I still can't get this done, everytime I run the macro without the form opened, it works fine but when I run it with the form opened, it just removes everything within the active row.

Can someone help, I can send the file.

Thanks

 

[shg]

Maybe ...

On Error Resume Next
Rows(ActiveCell.Row).Range("D1:Z1").SpecialCells(xlCellTypeConstants).ClearContents

 

[shyy]

Thanks I tried it but didn't work, I added the code to the control button.
It works however like before if I just run the macro without the form opened.

Private Sub CommandButton1_Click()
Call removecells
End Sub

Sub removecells()
On Error Resume Next
Rows(ActiveCell.Row).Range("D1:Z1").SpecialCells(xlCellTypeConstants).ClearContents
End Sub

 

[shg]

I had no difficulty running it from a button on a form.

The activesheet is a worksheet, not a chart?

If you take out the On Error statement, but ensure you have some constants in the row, what happens?

Anything else you can add?

 

[shyy]

it's an active worksheet, would it make a difference if the button was activex or not?

I will post some screen shots, or if you don't mind e-mail you the file in a bit. At work atm.

 

[shg]

Works fine for me from an ActiveX button on a sheet.

 

[shyy]

If the button is in a sheet and not a form, it works. Can I add a activex control button to a form? Don't see the option.