Userform - Prev & Next buttons that cycle through predefined cells, cell value will change to txtbox.value

[AirAssault]

Hello All,

I have a form on excel that has fields for item1, description 1, item2, description2, item 3, desc.3 etc.

Instead of having to select each one and type what I have, I created a userform that will cycle through each cell, and one textbox that would paste the txtbox.value to the selected item#/cell.value. Also the label would change so that you know you're on item1, or item2, or etc.

Also, if the cell is not blank it would display what the item#cell has in the textbox ( in case i misspelled something and decide to hit PREV or NEXT).

In this way I wanted to enter " this new item " in the textbox, hit a paste button ( or better in real time as it changes) and then item#1/cell("D6") would reflect the textbox value. When I hit the next button, the label would say item2 and when I enter " this second new item" in the textbox, then item#2/cell("D10") would reflect txtbox.value.

I'm not sure how to set multiple cells as a range, and I think a for loop is what I need in order to cycle through the range but I'm not really sure what I need. I've been doing a lot of things with VBA in excel in the last few weeks, but I know I've got a long way to go. I know a little scripting in 3ds max, but VBA is a whole other beast. I've been digging through these forums and google but I'm missing something.

Anyways, I just need the basic of what the code is. How would I put predefined cells in a range and cycle through that range only when I hit prev next buttons, and paste the txtbox value in real time or on command?

Any help would be appreciated, even if it's partial. Thanks in advance!

 

[dmt32]

Hi,
This is just an untested idea of how you could approach what you want to do with your Next / Previous Buttons but you will need to develop to meet your project need.

The following code places data in an array & you index the array elements (using Next / Previous) to return values to your textboxes. You will need to adjust the data range but in this example, I have used A1:C & lastrow) to fill three textboxes.

Place all code in your forms code Page:

Dim Data As Variant
Dim LastRow As Long
Dim r As Long


'===========================================


Private Sub UserForm_Initialize()


'worksheet with your data
'change name as required
    With Sheets("Data")


        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


        'the data range
        'adjust as required
        Data = .Range("A1:C" & LastRow).Value


    End With


    r = ActiveCell.Row - 1
    
    RangeRow r


End Sub


'=================================================


Private Sub NextRecord_Click()
    RangeRow xlNext
End Sub


'=================================================


Private Sub PrevRecord_Click()
    RangeRow xlPrevious
End Sub


Sub RangeRow(ByVal Direction As Long)
    r = IIf(Direction = xlPrevious, r - 1, r + 1)
    If r < 1 Then r = 1
    If r > LastRow Then r = LastRow
    'fill textboxes on userform
    'from Data Array
    With Me
        .TextBox1.Text = Data(r, 1)
        .TextBox2.Text = Data(r, 2)
        .TextBox3.Text = Data(r, 3)
    End With
End Sub

It is important that the variables shown are placed at the TOP of your forms code page. Also, If your Next / Previous Buttons have different names then adjust accordingly.


Hope suggestion of some help

Dave

 

[AirAssault]

Thanks, but it's not exactly what I needed.

All I really need is the code for a Next & Prev button to cycle the active selection through cells d6,d10,d14.

What I'm guessing is something like this:

Sub HitNext Button ()
Dim ItemArray As Variant
ItemArray = D6, D10,D14

make active selection d6, if you hit next button, active selection = d10,
End Sub

Sub hitPrev button()
go backwards in ItemArray

===================

I think I understand the concept of putting those cells in an array/string/range? and then wanting the next/prev button to cycle the active selection through that list, I just get lost on what the syntax should be.
I've been trying to rearrange what you've provided but no luck so far. Doesn't mean I won't stop trying and I will definitely let you know when something works

 

[AirAssault]

The code is starting to look like this
Dim rng As Range
Dim PTiRng As Range
Set PTiRng = ActiveSheet.Range("D6,d10,d14,d18,d22,d26")


For Each rng In PTiRng
.Activate

I think I'm almost there, I just need help

this is what the userform and the form look like: