Add Active X Command Button to sheet and keep Userform Open

[zoom38]

Hello, is there a way to create an active x command button on a worksheet while keeping a userform open?
When I use the following code to add the button on the worksheet, it closes the userform and I have to reload it manually.

Set myb = ActiveSheet.OLEObjects.Add(classtype:="Forms.CommandButton.1", link:=False, displayasicon:=False) 
        'set button properties
        With myb
            .Name = "CommandButton3"
            .Object.Caption = "Remove " & ActiveSheet.Name
            .Object.Font.Bold = True
            .Object.Font.Size = 11
            .Object.WordWrap = True
            .Top = 42.75
            .Left = 675
            .Width = 72
            .Height = 40
            .Shadow = True
            .Placement = xlMoveAndSize
            .PrintObject = False
        End With

By the way, the userform is Modeless.

Thanks
Gary

[zoom38]

Copying and pasting an existing button also closes the userform.

 ActiveSheet.Shapes.Range(Array("CommandButton2")).Select
         Selection.Copy
         ActiveSheet.Paste

[zoom38]

I have tried activating the code below from just about everywhere to reload the userform but it doesn't work. I can only get it to reload the userform from worksheet events such as Selection_Change, Activate or Deactivate but that takes a click one way or another to load the userform.

Sub ShowUserform1()
   If UserForm1.Visible = False Then
      UserForm1.Show False
   End If
End Sub

Demo5.xlsm
Anyone have an idea what is happening? I've uploaded an example. Try it from sheet 201.

Gary

[Kenneth Hobs]

You have a lot going on there so it is hard to see your problem as it errors on Open.

I recommend that you always use Option Explicit as the first line of code in objects like Modules and UserForms.

Consider rethinking your Protect/UnProtect scheme. When using Protect, be sure to use the option UserInterfaceOnly:=True. This lets your code modify things without UnProtect/Protect. That alone will likely help. I recommend protecting all sheets that way and then Unprotect the others as needed.

[zoom38]

Ken, it doesn't error on open for me but I streamlined the workbook even more and used option explicit throughout and unprotected only the active sheet in my code. However the userform still closes or unloads upon the creation of a button when a new sheet is added using my addsheet sub. I'm assuming that excel automatically puts it in Developer mode but I still cant get the userform to automatically reload after the new sheet is created without clicking somewhere relative to an event. I'm attaching a more streamlined workbook should you get a chance to look at it again. I tried unloading the userform before the sheet is added and then reloading but that didn't work. I think next I'm going to try having my code add a forms control command button rather than the active x command button but I'd be surprised if that changes anything.

Demo5.xlsm


Thanks
Gary

[Kenneth Hobs]

It still had an error on open. This was because you used Call Userform1.Show. Do not use Call. Remove those Call words. Call something is really only needed if you want to use () around the parameters for something.

Also, remove or comment out your Application.ScreenUpdating=False.

Once I did those, it did copy the sheet and with Userform1 still showing. Getting late so I would have to look at this more tomorrow. I did not see that the command button was added but maybe it was and my eyes are just too tired.

[zoom38]

Interesting that I don't get that error on open. I took out the call keywords and commented out the Application.ScreenUpdating=False but I still can't get the userform to stay open when I add a new sheet by clicking on the add 201 button on sheet 201. As a workaround I changed the code to add a forms control button instead of the active x button and it seemed to work. Tomorrow i'll have to learn how to code those buttons instead of the active x buttons that I've been using and am used to.

[Kenneth Hobs]

Since a Microsoft patch screwed up ActiveX some time back, I normally use Form controls now. I used to prefer ActiveX but stability is more important to me.

[snb]

Why don't you add the ActiveX control in 'design mode' ? property .Visible=False

The only thing you have to do in the userform is making the activex control visible: commandbutton3.visible=true

[Jan Karel Pieterse]

When you add any activeX control, your VBA project gets reset (similar to pressing the "End" toolbar button in the VBE). There is no workaround for this problem other than these:
1. make sure the ActiveX control already is on the sheet (as snb points out)
2. Make sure the running code is in a different VBAProject (an add-in for instance)
3. Don't use ActiveX controls to begin with (most recommended!)