VBA - Change from "PROPER" to Upper or Lower Case?

[mjmartin]

Hey all - Can someone tell me how to alter this line of VBA code so the data being pushed to my destination sheet is displayed in Upper Case (or Lower Case in other instances)

Sheet3.Range("a1000000").End(xlUp).Offset(0, 1).Value = Application.WorksheetFunction.Proper(UserForm1.TextBox2.Value)

I tried changing Proper to UCase or LCase - but got errors. For the sake of full disclosure I'm changing several similar lines, with some data coming from a TextBox and some from a ComboBox - I'm not sure if that makes a difference in the answer...

 

[Fluff]

How about

Sheet3.Range("a1000000").End(xlUp).Offset(0, 1).Value = Ucase(UserForm1.TextBox2.Value)

 

[mjmartin]

How about

Sheet3.Range("a1000000").End(xlUp).Offset(0, 1).Value = Ucase(UserForm1.TextBox2.Value)

That worked. Thanks.

So is the Application.WorksheetFunction not required? Why would that be there?

 

[Fluff]

Both UCase & LCase are VBA functions, whereas Proper is an Excel function
Your only need to use WorksheetFunction when calling an Excel function

 

[mjmartin]

Ahhhh! I see...thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback