CODEWITHSUNDEEP
vbaexcelexcel-2007
lonelydev101
lonelydev101

Reputation: 1901

Rename a sheet with TextBox value

I have a Form, and I wnat to rename the sheet. Actually, program does it correctly, but when I want to use .Activate function I get an error : Type mismatch

Worksheets.Add().name = UserForm1.txtNameSur

Worksheets(UserForm1.txtNameSur).Activate

I've also tried 

Worksheets("&UserForm1.txtNameSur&").Activate

Still the same.

Thanks!

Upvotes: 1

Views: 1183

Answers (1)

mtholen
mtholen

Reputation: 1661

GD Stefan,

The .Activate method of Worksheets is expecting either an index (integer or long) or a string, identifying the page to activate. You are passing an object in the form of an TextBox to the .Activate method. Try passing the value, rather than the whole object, as VBA gets confused as to what to do with that TextBox where it is expecting only a String or an Integer or Long variable.

i.e.:

Worksheets.Add().Name = UserForm1.txtNameSur.Value
Worksheets(UserForm1.txtNameSur.Value).Activate

That should work !

Upvotes: 1

Related Questions