Comparing input string against worksheet name

[ceecld]

Hi Guys,

first off i'm new to VBA programming, and i am looking to increase my knowledge with it to help increase the efficeincy of certain manual process which i carry out in my job.


Anyway i have had a quick search and cant seem to find the answer to my question in this forum.

The problem i have is that i want the user to enter the name of a worksheet into an input box excel then creates worksheets based on this. however if sheets with this name already exist i want to call an error procedure. below is a sample of the code which i was hoping would check if the input string equals any worksheet name then presents a message box. it returns a type mismatch error though, and im a little puzzled by this as i though the worksheet name would be a string. ( i have also tried using variants)

any suggestions on how i resolve this issue?


Thanks in adavce for your help.

Dim motor_name As String
Dim wks_name As String
Dim mysheet As Worksheet

motor_name = InputBox("Enter Motor Name", "Add Motor")

For Each mysheet In Worksheets
wks_name = mysheet.Name
If wks_name = motor_name Then
MsgBox "This motor already exists", "Error Message"
End If
Next mysheet

 

[PHV]

type mismatch error
Which line of code raises this error ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

OK, on the MsgBox line, I guess.
Try this:
MsgBox "This motor already exists", vbCritical, "Error Message"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ceecld]

Jeezo! school boy error from me there!

Sometimes its good just to get someone else to cast their eyes over it!

Thanks for your help.

Chris