Doubleclick event interacting with Userform

[jeffreybrown]

Have a userform with two option buttons (one delete and one insert), one textbox and an Ok and cancel button.

In the worksheet module for one sheet I have a Private Sub Worksheet_BeforeDoubleClick event that will either insert or delete rows.

How can I get the Private Sub Worksheet_BeforeDoubleClick event to interact with a userform?

In the Private Sub Worksheet_BeforeDoubleClick event I put the line frmInsertDelete.show which shows the form and the default on the form is optDelete so in the event I also put if optInsert = True then goto Insertrows but this did not work.

The textbox is for the user to enter how many rows the user wants to add.

Any suggestions?

[Leith Ross]

Hello Jeff,

Have you set the UserForm's ShowModal property to False?

[jeffreybrown]

Hi Leith,

No I did not change the ShowModal to False, but I have now. What happens now is I double click on a cell and the userform shows, but the macro still fires and inserts rows.

What am I still missing?

Please Login or Register  to view this content.

[Leith Ross]

Hello Jeff,

This will fix the problem.

Please Login or Register  to view this content.

[jeffreybrown]

Thanks again Leith for thecode, but it doesn't seem to want to work correctly as expected.

At first I had...

Please Login or Register  to view this content.

...so when the form opens optInsert is checked by default and it works, but then I doubleclick again to delete the rows just added and it still adds rows, no delete. I changed the UserForm_Initialize to optDelete = True and the delete works no matter whether I choose the Insert or Delete.

I took the optInsert = True out of the UserForm_Initialize and no matter whether I choose the Insert or Delete nothing happens. I stepped through the code and sure enough both are false.

Also, if I select Cancel on the form it still runs the doubleclick event.

[snb]

in the userform:

Please Login or Register  to view this content.

You should choose: use the UI of the worksheet or the userform.

[jeffreybrown]

Hi Leith,

I believe I have figured on a solution. When the insert or delete button is clicked I can have that value stored to a cell such as the active sheet F1 and then in the code just reference that cell. I've tested this method and it works fine. When the code executes I will have the contents of F1 removed so the code is ready to run again.

Hi snb,

I will give this a go.

[jeffreybrown]

Thanks again guys for all the help, I have a workable solution.

For the Cancel button on the form I went with...

Please Login or Register  to view this content.

...and it seems to do the trick.