Macro is not removing cells correctly from a form control button

shyy

Well-known Member
Joined
Nov 6, 2008
Messages
1,484
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

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Maybe ...
Code:
On Error Resume Next
Rows(ActiveCell.Row).Range("D1:Z1").SpecialCells(xlCellTypeConstants).ClearContents
 
Upvote 0
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

Code:
Sub removecells()
On Error Resume Next
Rows(ActiveCell.Row).Range("D1:Z1").SpecialCells(xlCellTypeConstants).ClearContents
End Sub
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Works fine for me from an ActiveX button on a sheet.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top