Moving within a specified range after User Input

[34sweetness]

I have a worksheet with a dynamic named range (sData) and I have an ActiveX listbox embedded on the worksheet from which specific data is chosen by the user. There is also a command button that when clicked transfers the information chosen from the list box to a specific place in the dynamic range.

This is where I am having trouble.

Once the information is transferred to a specific place in the range (after the command button click) I want the next input location in the dynamic range to be “active”, so that the next selection from the listbox and subsequent button click will put the information from the listbox in the correct location.

The dynamic range is set up with locations numbered from 1 (the initial starting location in the range) to a set number determined by another defined variable (n). So I want the next active cell in the range to increment up to the nth number (oh yeah, the numbers are not in any order and are set in the range as a formula…)

So, the first active cell in the named range (sData) is one cell to the right of “1” (the value of the cell, and the starting point for data entry). The user selects a value from the listbox, clicks the command button, and the data is transferred to the range. I then want the next active cell to be one cell right of the cell with the value “2”. Then information from the listbox can be selected, the command button clicked, and data entered into the range next to “2”…then so forth until the nth value is reached.

This is what I have so far in regards to the “moving” of the active cell (macro assigned to the command button):

Please Login or Register  to view this content.

This works when the button is clicked for the first time (active cell moves to the right of “2”), but it stays at “2” no matter how many times the command button is “clicked”.

How do I get the “counter” (i) to increment each time the command button is clicked, until it reaches n?

[royUK]

You don't need to Select any cells to add data to them using VBA and I suspect you could use the ListBox_Click event instead of the CommandButton. I'm not sure what you re doing your code seems to be looking for a value of 3.

Attach the workbook.