Code in a Module is working but in an Userform not

[CLE81]

Hello,

I have a code written in a Module. In this module the code is working. But when I place it in a Userform the code with a small amount of modifactions it's not working anymore.

The code has to add some worksheets with names on another worksheet. If the sheet with the same name already exists it has to do nothing.

The error message I got is ByRef - argument type mismatch. I've checked the code many times but I don't see the error.
Line where the error is marked during errorhandling, is the line where the Names_of_Sheets is written.

Can some one helpme find the problem?

Private Sub UserForm_Activate()

' ---------------------- Declare variables -------------------------------------------
    ' Declare variables
    Dim No_Of_Sheets_to_be_Added, i, m, AmountLanguages, First_Column, Last_Column As Integer
    Dim aw_name, sh_name, HMI_Select, HMI_config, Language As String
    Dim Names_of_sheets As Range
    
    aw_name = ActiveWorkbook.Name
                    
    HMI_Select = "Select"
    HMI_config = "HMI_config"
        
    'Set first & last column in HMI config
    First_Column = Sheets(HMI_config).UsedRange.Columns(1).Column
    Last_Column = Sheets(HMI_config).Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column

    Names_of_sheets = Sheets(HMI_config).Range(Cells(First_Column, 1), Cells(Last_Column, 1))
    No_Of_Sheets_to_be_Added = Sheets(HMI_config).Cells(1, ActiveSheet.Columns.Count).End(xlToLeft).Column
    
   'Bepaal aantal toegepaste talen
    AmountLanguages = Sheets(HMI_Select).Cells(Rows.Count, 5).End(xlUp).Row
    
    For m = 2 To AmountLanguages
            'Set language
            Language = ActiveWorkbook.Sheets(HMI_Select).Cells(m, 5)
            
            For i = 1 To No_Of_Sheets_to_be_Added
                
                sh_name = "#" & Names_of_sheets.Cells(1, i).Value & "_" & Language
                
                'Only add sheet if it doesn't exist already and the name is longer than zero characters
                If (Sheet_Exists(sh_name) = False) And (sh_name <> "") Then
                    Worksheets.Add(After:=Sheets(Sheets.Count)).Name = sh_name
                End If

                  '--------------------- Progressbars -------------------------------
                                
                    Dim pctCompl1, pctCompl2 As Single
                    Dim MainInfo, SubInfo1, SubInfo2 As String
                    
                    'Main description
                        MainInfo = "Creating sheet...." & sh_name & " | " & Language & " | "
                    'Bar 1
                        pctCompl1 = Round((m / AmountLanguages) * 100)
                        SubInfo1 = m & "/ " & AmountLanguages & " | Languages"
                    'Bar 2
                        pctCompl2 = Round((j / No_Of_Sheets_to_be_Added) * 100)
                        SubInfo2 = i & "/ " & No_Of_Sheets_to_be_Added & " | sheets"
                        
                     'Call progressbar
                        progress pctCompl1, pctCompl2, MainInfo, SubInfo1, SubInfo2
                        
                    '-----------------------------------------------------------------
            Next i
    Next m

    'Openen voorblad
    ActiveWorkbook.Sheets("Voorblad").Activate
    Application.ScreenUpdating = True

    Unload Me
End Sub

Function Sheet_Exists(sh_name As String) As Boolean

    Dim Work_sheet As Worksheet

    Sheet_Exists = False

    For Each Work_sheet In ThisWorkbook.Worksheets

        If Work_sheet.Name = sh_name Then
            Sheet_Exists = True
        End If
    Next
End Function
Sub progress(pctCompl1, pctCompl2 As Single, MainInfo, SubInfo1, SubInfo2 As String)

    UserForm_Fill_HMI_sheets.LabelMainInfo.Caption = MainInfo

    UserForm_Fill_HMI_sheets.LabelSubInfo1.Caption = SubInfo1
    UserForm_Fill_HMI_sheets.LabelBar1.Caption = pctCompl1 & " %"
    UserForm_Fill_HMI_sheets.LabelProgress1.Width = pctCompl1 * 2
    
    UserForm_Fill_HMI_sheets.LabelSubInfo2.Caption = SubInfo2
    UserForm_Fill_HMI_sheets.LabelBar2.Caption = pctCompl2 & " %"
    UserForm_Fill_HMI_sheets.LabelProgress2.Width = pctCompl2 * 2
    
    DoEvents
End Sub

 

[dmt32]

When you pass an argument ByRef you must match the data type in the argument passed to the parameter in the procedure or function.

Your declarations in the calling code have different data types, for instance, in the Sheet_Exists function, the parameter is declared as String data type but the variable sh_name in the calling procedure used to pass values to it, is declared as a Variant data type.

With your declarations, only the last variable in both lines is declared as the specified data type the others are all Variants.

Dim No_Of_Sheets_to_be_Added, i, m, AmountLanguages, First_Column, Last_Column As Integer

Dim aw_name, sh_name, HMI_Select, HMI_config, Language As String

In VBA, you need to explicitly, declare each variable with the required data type like this

Dim No_Of_Sheets_to_be_Added As Integer, i As Integer, m As Integer, AmountLanguages As Integer, First_Column As Integer, Last_Column As Integer

Dim aw_name As String, sh_name As String, HMI_Select As String, HMI_config As String, Language As String

As you don’t really need to pass values ByRef then suggest that you update both the codes to ByVal

Function Sheet_Exists(ByVal sh_name As String) As Boolean

Hopefully, all this will help resolve your issue.



Hope Helpful



Dave

 

[CLE81]

Hello Dave,

Thanks for the quick reply.

I 've changed my code like you suggest but it didn't solve the problem. Also when place a ByVal instuction I get an other error when I run this particualar piece of code.

Message: "Run-time error 1004: Application-defined or Object-defined error"

The error indictates the line:

Names_of_sheets = Sheets(HMI_config).Range(Cells(First_Column, 1), Cells(Last_Column, 1))

When I hoover the variables the values are OK but I don't understand whats wrong.

 

[Fluff]

As you are assigning a range to the variable, you need to put the word Set as the start of that line.

 

[CLE81]

Fluff,

Yes, that's correct.

I ve got on another sheet 1 row with devices names which will be representated in new sheets thats where for the range is.

puttin Set before the line but it didn't solve the problem.

 

[Fluff]

You also need to qualify the cells, try

    With Sheets(HMI_config)
      Set Names_of_sheets = .Range(.Cells(First_Column, 1), .Cells(Last_Column, 1))
    End With

 

[CLE81]

Perfect!

That was the issue. Now it;s working. Very very much thanks....

 

[CLE81]

Thread can be closed

 

[Fluff]

Glad we could help & thanks for the feedback.