Hello Everyone,
I've been searching through the forums and have not found anything that will answer my question. I think the answer is probably fairly simply, but I have not been able to determine it on my own. I have not written in VB in years and this is the first time I have ever written in VBA so please excuse some of my coding.
I am creating a user form that will do several things:
1). The user will be able to select a worksheet from a combo box, make it active, and jump to that sheet by way of a command button.
2). The user will be able to automatically format the active worksheet (with preset formatting conditions) by way of a command button (This button is not yet coded).
3). The user will be able to delete a worksheet by selecting it in the combo box and pressing a command button.
4). The user will be able add a new sheet to the workbook by naming the sheet in a text box hitting a command button. The click action will add the sheet to the combo box as well.
5). And lastly, the user will be able to Exit the macro and save the workbook by pressing the Exit command button.
The is issue that I am having revolves around the "Add New Sheet" command button. I have coded the button to read the text in the text box and name the sheet accordingly. However, I am having trouble handling an error that comes up if the new sheet has the same name as the old sheet. I was hoping someone could help me see what I'm doing wrong. It would be greatly appreciated.
I've been searching through the forums and have not found anything that will answer my question. I think the answer is probably fairly simply, but I have not been able to determine it on my own. I have not written in VB in years and this is the first time I have ever written in VBA so please excuse some of my coding.
I am creating a user form that will do several things:
1). The user will be able to select a worksheet from a combo box, make it active, and jump to that sheet by way of a command button.
2). The user will be able to automatically format the active worksheet (with preset formatting conditions) by way of a command button (This button is not yet coded).
3). The user will be able to delete a worksheet by selecting it in the combo box and pressing a command button.
4). The user will be able add a new sheet to the workbook by naming the sheet in a text box hitting a command button. The click action will add the sheet to the combo box as well.
5). And lastly, the user will be able to Exit the macro and save the workbook by pressing the Exit command button.
The is issue that I am having revolves around the "Add New Sheet" command button. I have coded the button to read the text in the text box and name the sheet accordingly. However, I am having trouble handling an error that comes up if the new sheet has the same name as the old sheet. I was hoping someone could help me see what I'm doing wrong. It would be greatly appreciated.
Code:
If txtBox.Text = "" Or txtBox.Text = cmbSheet Then
'User didnt input a name for the worksheet
MsgBox "Please name your worksheet correctly!", vbCritical, "Input Box Error"
'ElseIf txtBox.Text = cmbSheet.Text Then
'User tried to assign a sheet already named that
'MsgBox "You have tried to assign a name that is already in use. Please input correct name.", vbCritical, "Input Box Error"
ElseIf txtBox.Text <> "" And txtBox.Text <> cmbSheet Then
'Add Named sheet to combobox and workbook. Reset textbox.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = txtBox.Text
Me.cmbSheet.AddItem txtBox.Text
txtBox.Text = ""
End If
