Solved: Looping around a userform textbox

[Rob342]

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]

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]

hi Rob.

you can adopt this

[VBA]
Sub incr_lett()

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

MsgBox msg

End Sub
[/VBA]

[Rob342]

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]

You need to set the .Locked property of the text box to False. Then a line like this will fill in the proper value
[VBA]With Range("CorpIdBranch")
Me.TxtClmNo.Text = CStr(.Offset(.Rows.Count, 0).End(xlUp).Offset(1, 3).Value)
End With
[/VBA]

[Rob342]

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]

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]

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]

I don't understand what you are doing so I couldn't answer when this part of it should be done.

[Rob342]

Thanks to you both most appreciated , i can work with both of these options.

Regards
Rob