PDA

View Full Version : Solved: space bar



av8tordude
04-25-2011, 09:06 PM
How can I prevent the first character that is type from being a space (created by the space bar)?

GTO
04-25-2011, 11:52 PM
Maybe:Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
Target.Value = LTrim(Target.Value)
End If
End Sub

av8tordude
04-26-2011, 01:37 AM
Hi GTO,

Sorry, but I forgot to mention it should be applied in a userform textbox? How can i apply this to a textbox?

GTO
04-26-2011, 01:44 AM
Did you try the exit event?

av8tordude
04-26-2011, 01:50 AM
not sure what you mean...apply the same code in the exit event? I'll get an error.

GTO
04-26-2011, 02:41 AM
not sure what you mean...apply the same code in the exit event? I'll get an error.

I meant the exit event of the textbox. In a new/blank workbook, create a userform with three textboxes.
Option Explicit

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim MeCtl As MSForms.TextBox
Set MeCtl = Me.ActiveControl
TrimLeft MeCtl
Me.Caption = ">" & MeCtl.Value & "<"
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim MeCtl As MSForms.TextBox
Set MeCtl = Me.ActiveControl
TrimLeft MeCtl
Me.Caption = ">" & MeCtl.Value & "<"
End Sub

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim MeCtl As MSForms.TextBox
Set MeCtl = Me.ActiveControl
TrimLeft MeCtl
Me.Caption = ">" & MeCtl.Value & "<"
End Sub

Private Function TrimLeft(ByRef tb As MSForms.TextBox)
tb.Value = LTrim(tb.Value)
End Function
You can watch the userform's caption to see that the values entered into whichever textbox are trimmed on the left (leading spaces) upon exiting the textbox.

What exactly are you wanting to do? I ask as I would suspect that trimming in the textbox is not the issue. Wouldn't we be more likely be concerned with what is happening with the textbox's value as it affects other parts of the code (ie - the val stored in the variable). I hope that makes sense.

Mark

av8tordude
04-28-2011, 06:27 AM
Thank you Mark. your code helps a lot