Code in a Module is working but in an Userform not

CLE81

New Member
Joined
Oct 23, 2020
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,790
Office Version
  1. 2019
Platform
  1. Windows
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.

Code:
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

Code:
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



Rich (BB code):
Function Sheet_Exists(ByVal sh_name As String) As Boolean

Hopefully, all this will help resolve your issue.



Hope Helpful



Dave
 

CLE81

New Member
Joined
Oct 23, 2020
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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:

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
As you are assigning a range to the variable, you need to put the word Set as the start of that line.
 

CLE81

New Member
Joined
Oct 23, 2020
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
You also need to qualify the cells, try
VBA Code:
    With Sheets(HMI_config)
      Set Names_of_sheets = .Range(.Cells(First_Column, 1), .Cells(Last_Column, 1))
    End With
 
Solution

CLE81

New Member
Joined
Oct 23, 2020
Messages
19
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Perfect!

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,162
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,140,922
Messages
5,703,176
Members
421,280
Latest member
Jaycee01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top