Looping Through Values using a Listbox

[mangeshmm]

All - seeking your help on this issue. I have a table of values (lets say 5 rows and 3 columns). I have a userform which has 3 textboxes on it. What I want to do is to, turn by turn, display each of the values in each row across the 3 boxes (each textbox would hold a value from each col - so textbox1 would have row1, col1; textbox 2 would have row1, col2; textbox 3 would have row 1, col3)

The userform has 2 commandbuttons "Yes", "No". When the user presses either buttons, the values from textbox will change from row1 to row2 to row3 to row4 to row5.

Any idea how this can be achieved?

What I have already attempted is to use a loop, but that runs straight down to row 5 - since there is nothing to 'halt' the display at each row!

 

[MickG]

Try this, Based on range "A1:C5":-

[COLOR="Navy"]Sub[/COLOR] MG14Sep25
Private [COLOR="Navy"]Sub[/COLOR] CommandButton1_Click()
'[COLOR="Green"][B]"Yes button"[/B][/COLOR]
Call Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] CommandButton2_Click()
'[COLOR="Green"][B]No Button"[/B][/COLOR]
Call Txt
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]




[COLOR="Navy"]Sub[/COLOR] Txt()
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
    Static c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range("A1:A5")
    c = c + 1
    Me.TextBox1 = Rng(c)
    Me.TextBox2 = Rng(c).Offset(, 1).Value
    Me.TextBox3 = Rng(c).Offset(, 2).Value
    c = IIf(c = Rng.Count, 0, c)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick


http://www.dec.org.uk/

 

[mangeshmm]

Thanks Mick - can you decode this for me so I know what exactly each part of the code achieves? While I havent tested it out intuitively I am assuming that the static variable ensures that each button calling the code does not mean that 'c' is reset!

Wow! Let me bung this in and see how it works! Thanks a lot for your help in advance. Happy to reach out again if I need anything more

 

[MickG]

You're welcome

The static "c" means that the value of "c" keeps counting each time you click one of the buttons and does not reset to 0, except when the count reaches the number of rows in the range.