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?
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