PDA

View Full Version : Solved: Looping around a userform textbox



Rob342
11-28-2011, 04:30 PM
Hi all

I have a userform txtbox called TxtLineRef, whats the best way to insert "A "on the first loop then B Then C etc upto Z
I have a dynamic array on sheet 3 called Lineref, can i call the index of the array or would it be best to loop from 1 to 26 inserting the letters on each loop.
Can do with a combo box but didn't want the user to have access to this field.

mikerickson
11-28-2011, 05:03 PM
What loop?
Insert the characters where?
What is in Lineref and what does that have to do with the text box?

Why could a user access a combobox but not a text box?

If you attach a small, representative workbook that illustrates your situation, it would help folks understand what you are after.

mancubus
11-28-2011, 05:04 PM
hi Rob.

you can adopt this


Sub incr_lett()

For i = 65 To 90
msg = msg & Chr(i) & vbCr
Next

MsgBox msg

End Sub

Rob342
11-29-2011, 02:02 AM
Mike
Have attached a workbook, its the Line Ref in RED trying to update ie
Start at line A when the details are added, if the user wants to add another clm then this field would get updated to B automatically.

Mancubus

That would work, thanks for that never thought to use Chr ()

Rob

mikerickson
11-29-2011, 08:00 AM
You need to set the .Locked property of the text box to False. Then a line like this will fill in the proper value
With Range("CorpIdBranch")
Me.TxtClmNo.Text = CStr(.Offset(.Rows.Count, 0).End(xlUp).Offset(1, 3).Value)
End With

Rob342
11-29-2011, 02:23 PM
Hi Mike

Have tried the code it gives me "F" all the time. It should start at A , then when the user hits the Add button then the line should increment to "B" and so on.
There are only 5 branches there in the test but there could be 100.


Rob

mikerickson
11-29-2011, 05:05 PM
Which line do you want returned?
That routine returns the letter from the row after the last filled row.

Are you looking for somethign like


Static IncrimentNumber As Long

If IncrimentNumber < 64 Then IncrimentNumber = 64
IncrimentNumber = IncrimentNumber + 1
TextBox1.Text = Chr(IncrimentNumber)

Rob342
11-30-2011, 01:25 PM
Hi Mike

Thanks that works all ok, where in your opinion would be the correct place to slot that code in ie when that textbox gets focus or before update , afterupdate ?

Rob

mikerickson
11-30-2011, 02:02 PM
I don't understand what you are doing so I couldn't answer when this part of it should be done.

Rob342
12-01-2011, 08:43 AM
Thanks to you both most appreciated , i can work with both of these options.

Regards
Rob