Form VBA and deleting cells while form is open

[mikechambers]

I'm trying to run code on a sheet while my form is still open. I have simplified this code a lot to isolate the issue in the simplest form. I have my main macro launching the form, and vbModeless seems to do nothing for me. Then when the DropDown list is changed, it runs another macro while the form is still open, which it needs to be open. The problem is, when it is changed, it runs the code Change macro, which then runs Macro1. This is where I have the problem. It fails when I try to delete some cells on a sheet. At this point where it fails, it will let me enter text into a cell thru VBA, but it will not let me delete cells. And the sheet is not protected.

I get the following error:

Runtime error 1004

Delete method of Range class failed

Any ideas? Seems like a simple mistake I'm making...


***in main module***
Sub LaunchForm()
UserForm1.Show vbModeless
End Sub


***in form code***
Private Sub UserForm_Initialize()
DropDown1.RowSource = "Sheet1!C1:C10"
End Sub

Private Sub DropDown1_Change()
Application.EnableEvents = False
List2Filter = DropDown1.Value
Run "Macro1"
End Sub

***in main module***
Private Sub Macro1()
Application.ScreenUpdating = False
Sheet1.Range("W42") = "hello" 'THIS WORKS
Sheet1.Range("W42").Delete shift:=xlUp 'THIS FAILS
End Sub

 

[mikechambers]

How do I edit posts??

 

[My Aswer Is This]

Not possible after 10 minutes.

How do I edit posts??