0

My macro runs the userform initialize macro when copying one sheet to another. On the first sheet I have a button which is starting up the userform and my macro is copying this button to the next sheet along with the data. The macro worked perfectly fine before, however now the userform button seems to call the initializing sub from the userform even though it is not called upon in the code. It happens when copying the sheet. Below is the code.

Sub ExampleSUB()

'Delete Sheets After Sheet

    Dim i As Long
    Dim j As Long
    Dim WS As Worksheet
Dim StartCell As Range
Dim Myvalue As String

    j = 0
    For i = 1 To Sheets.Count
        If Sheets(i).Name = "ABC" Then
            j = i
        End If
    Next i

    If j = 0 Or j = Sheets.Count Then
    End If

    Application.DisplayAlerts = False
        For i = Sheets.Count To j + 1 Step -1
            Sheets(i).Delete
        Next i
    Application.DisplayAlerts = True


Myvalue = "Example"

'Create New Sheet
Set WS = Sheets.Add(after:=Sheets(Worksheets.Count))
    WS.Name = Myvalue

'If Sheet is VeryHidden
Dim WasHidden

    Application.ScreenUpdating = False
    If Sheets("total BU cost per product").Visible = xlSheetVeryHidden Then
        Sheets("total BU cost per product").Visible = xlSheetVisible
        WasHidden = True
    End If

calls initialize sub in following section

    Sheets("total BU cost per product").Select
    Cells.Select
    Selection.Copy
    Sheets(Myvalue).Select
    Range("A1").Select
    ActiveSheet.Paste

Below is code from userform it mysteriously calls:

Private Sub UserForm_Initialize()

With ListBox1
    .AddItem "Denmark"
    .AddItem "Sweden"
    .AddItem "Norway"
    .AddItem "Finland"
    .AddItem "Luxembourg"
    .AddItem "Germany"
    .AddItem "UK"
    .AddItem "Finland"
End With

OptionButton3.Value = True

End Sub

The userform is called through below

private sub showuserform ()
selection.show
end sub

but when I run through the code (F8), it doesn't seem like it calls the showuserform sub

Here is the link to the userform I use: https://www.excel-easy.com/vba/examples/multiple-list-box-selections.html

Why does it automatically run the userform sub?

Gisse
  • 57
  • 5
  • I suggest starting [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) on how to avoid using select, as it is bad practise. – Plutian Dec 03 '19 at 11:46
  • Thanks. Could this solve my problem? Do you have an idea why my macro suddenly calls the userform? – Gisse Dec 03 '19 at 14:25
  • I don't see why. Can you give us your complete code? Because this is just your copy-paste action. The trigger might be elsewhere. Please include the code linked to the button which calls your userform as well. – Plutian Dec 03 '19 at 14:34

0 Answers0