'Copy Sheet and Update Active X Combo Box
I have a workbook, which includes a data entry form that will copy data from the input sheet to specific sheets based off the Active X Combo Box which includes all the sheet names. I have given the user the ability to add a new page, which copies a template page and sorts all the pages alphabetically. However, the worksheet. Activate which is where the code is stored to populate the Active X Combo Box with all the sheet name does not populate. Also, every couple times testing this code it doesn't name the sheet properly, renaming it Template (2) and places it at the end, however sometimes it correctly names the sheet and places it properly. I am not sure why the code doesn't always work as expected and the combo box is not populating by the worksheet.activate event. Could you please help me, please see the code below.
Private Sub cbAddPlant_Click()
Dim wb As Workbook
Dim ws As Worksheet
Dim ShCount As Integer
Dim i As Integer
Dim j As Integer
Application.ScreenUpdating = False
ShCount = Sheets.Count
Set wb = ThisWorkbook
If tbPlantName.Value = "" Then
MsgBox "Please Enter A Plant Name!", vbCritical
Exit Sub
Else
wb.Worksheets("Template").Copy Before:=wb.Worksheets(Worksheets.Count)
ActiveSheet.Name = tbPlantName
End If
For i = 2 To ShCount - 1
For j = i + 1 To ShCount
If UCase(Sheets(j).Name) < UCase(Sheets(i).Name) Then
Sheets(j).Move Before:=Sheets(i)
End If
Next j
Next i
wb.Worksheets("Input").cbPlantName.Clear
For i = 2 To ShCount - 1
wb.Worksheets("Input").cbPlantName.AddItem Sheets(i).Name
Next
Wb.Worksheets("Input").Activate
Unload AddPlant
Application.ScreenUpdating = True
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
