Spin Button Fun

[adam]

Hi anyone,

I'm having a worksheet with the name of "Dates" where my data starts from the row 5 meaning the column headings are on the 4th row. I have a total of 8 columns in the sheet.

The sheet keeps on updating daily as I enter data to the respective columns. And this data keeps on updating a list box simultaneously.
I have added two spin buttons to arrange the rows as required by the user. Meaning to move the rows up & down from both the list box & the worksheet simultaneously.

But the spin button that I have added does not seem to be working as required. For this reason I have put up the spin button code hoping to get a suggestion why it is not working for me.

In my opinion may be I have done something wrong with the ranges.
Anyways, I would be happy if someone could let me know what is wrong in my code.

Thanks in advance & here is the code;

Code: Select all

Option Explicit
Private Sub UserForm_Activate()
    populateBox 'populates listbox on load
End Sub

Private Sub SpinButton1_SpinDown()
    Dim i As Long
    'change line below to reflect location of your list, and update listbox name
    With ThisWorkbook.Sheets("Dates")
        i = Listbox2.ListIndex + 1
        If i > 0 And i < .UsedRange.Rows.Count - 1 Then
            .Cells(i + 1, 1).Value = .Cells(i + 2, 1).Value
            .Cells(i + 2, 1) = Listbox2.Value
            populateBox
           Listbox2.Selected(i) = True
        End If
    End With
End Sub
Private Sub SpinButton1_SpinUp()
    Dim i As Long
    i = Listbox2.ListIndex + 1
    If i > 1 And i < ActiveSheet.UsedRange.Rows.Count Then
        With ThisWorkbook.Sheets("Dates")
            .Cells(i + 1, 1).Value = .Cells(i, 1).Value
            .Cells(i, 1) = Listbox2.Value
        End With
        populateBox
        Listbox2.Selected(i - 2) = True
    End If
End Sub
Public Sub populateBox()
   Listbox2.Clear
    Dim cell As Range
    'change to location of your list
    With ThisWorkbook.Sheets("Dates")
        If .UsedRange.Rows.Count > 1 Then
            For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))
                Listbox2.AddItem cell.Value
            Next cell
        End If
    End With
End Sub

[HansV]

The SpinButton1_SpinUp and SpinButton1_SpinDown event procedures only move values in column A up and down: expressions such as .Cells(i + 1, 1) refer to cells in the first column.
You'll have to add code to move values in columns B through H too. Here is a possible modification for the SpinDown event procedure, I'll leave it to you to modify the SpinUp procedure.

Code: Select all

Private Sub SpinButton1_SpinDown()
    Dim i As Long
    Dim c As Long
    Dim varTemp As Variant
    'change line below to reflect location of your list, and update listbox name
    With ThisWorkbook.Sheets("Dates")
        i = ListBox2.ListIndex + 1
        If i > 0 And i < .UsedRange.Rows.Count - 1 Then
            For c = 1 To 8
                varTemp = .Cells(i + 1, c).Value
                .Cells(i + 1, c).Value = .Cells(i + 2, c).Value
                .Cells(i + 2, c) = varTemp
            Next c
            populateBox
           ListBox2.Selected(i) = True
        End If
    End With
End Sub

[adam]

Thanks for the modification of the SpinButton1_SpinDown() code.

But, during my explanation at Post=18722,I have mistakenly skipped out some lines.

I’m sorry for that & I do apologize for any inconveniences. Hence this is how the remaining part of the explanation goes.
The code that populates my list box is as follows.

Code: Select all

Private Sub UserForm_Initialize()
MakeList
End Sub

Sub MakeList()
ListBox2.List = Range("Data").SpecialCells(xlCellTypeVisible).Value
End Sub

My intention of the code at Post=18722 was to be focused on the part of the code related to the Spin buttons.

So If I’m going to use the spin button code with the above code, should the line "populateBox" be changed with "MakeList"

Because I’m getting debug message with a yellow highlighting of the line "populateBox"

But doing so does not make the code work. What might be the complication?

[HansV]

You can't combine the spinbutton code with MakeList. The spinbutton code will move a row up or down regardless of whether it is visible or not.

[adam]

Does it mean I should add the code

Code: Select all

Private Sub UserForm_Activate()
    populateBox 'populates listbox on load
End Sub

to the code at Post=18739?

[HansV]

No, you can't use the code from Post=18739. The userform should work fine without it.

[adam]

Wouldn't the code work as,

Code: Select all

Private Sub UserForm_Activate()
    populateBox 'populates listbox on load
End Sub

Private Sub UserForm_Initialize()
MakeList
End Sub

Sub MakeList()
ListBox2.List = Range("Data").SpecialCells(xlCellTypeVisible).Value
End Sub

Public Sub populateBox()
   Listbox2.Clear
    Dim cell As Range
    'change to location of your list
    With ThisWorkbook.Sheets("Dates")
        If .UsedRange.Rows.Count > 1 Then
            For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))
                Listbox2.AddItem cell.Value
            Next cell
        End If
    End With
End Sub

By changing the line

Code: Select all

For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))

To adjust according to the sheet I have mentioned in Post=18722

[HansV]

Modifying the spinbutton code would become far too complicated.

[adam]

As you have mentioned, modifying the spinbutton code would become far too complicated.

But the code in my reply at Post=18751 does spin down the rows to the rows where I take them. But the problem is I'm unable to view the complete 8 columns that is in the worksheet with my list box and the data in the list box starts after two empty rows. Wouldn't it be related to the following code

Code: Select all

Public Sub populateBox()
   Listbox2.Clear
    Dim cell As Range
    'change to location of your list
    With ThisWorkbook.Sheets("Dates")
        If .UsedRange.Rows.Count > 1 Then
            For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))
                Listbox2.AddItem cell.Value
            Next cell
        End If
    End With
End Sub

Specially with the part

Code: Select all

        If .UsedRange.Rows.Count > 1 Then
            For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))
                Listbox2.AddItem cell.Value

[HansV]

What is the range that contains your data?

[adam]

Hi anyone,

I'm having a worksheet with the name of "Dates" where my data starts from the row 5 meaning the column headings are on the 4th row. I have a total of 8 columns in the sheet.

My Data starts from A5 with 8 columns up to H.

[HansV]

Sorry, I overlooked that. You should modify the code to take that into account. The present code assumes that the data begin in row 2, not in row 5.

[adam]

Do you mean I have to change

Code: Select all

For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))

with

Code: Select all

For Each cell In .Range(.Cells(5, 1), .Cells(.UsedRange.Rows.Count, 1))

I do get rid of the empty rows with the above change. But cannot view the complete 8 columns.

Here's a scrapped version of the workbook for better understanding of my problem.

[HansV]

OK, scrap populateBox and the UserForm_Activate code, and use this code for the SpinDown event:

Code: Select all

Private Sub SpinButton1_SpinDown()
    Dim i As Long
    Dim c As Long
    Dim varTemp As Variant
    With Range("Data")
        i = ListBox2.ListIndex
        If i > -1 And i < .Rows.Count - 2 Then
            For c = 1 To 8
                varTemp = .Cells(i + 1, c).Value
                .Cells(i + 1, c).Value = .Cells(i + 2, c).Value
                .Cells(i + 2, c) = varTemp
            Next c
            MakeList
           ListBox2.Selected(i + 1) = True
        End If
    End With
End Sub

[adam]

I'll leave it to you to modify the SpinUp procedure.

Here's how I have modified the code. I would be happy if you could comment on this. The rows does spin up with this modification.

Code: Select all

Private Sub SpinButton1_SpinUp()
    Dim i As Long
    Dim c As Long
    Dim varTemp As Variant
    With Range("Data")
        i = ListBox2.ListIndex - 1
        If i > -1 And i < .Rows.Count - 2 Then
            For c = 1 To 8
                varTemp = .Cells(i + 1, c).Value
                .Cells(i + 1, c).Value = .Cells(i + 2, c).Value
                .Cells(i + 2, c) = varTemp
            Next c
            MakeList
           ListBox2.Selected(i - 0) = True
        End If
    End With
End Sub

[HansV]

That looks good. You can omit the - 0 in i - 0, for subtracting 0 doesn't do anything.

[adam]

Thanks for the suggestion Hans.

One more query if I may add. The spin button does not seem to work when the worksheet and the list box gets filtered simultaneously as in Post=18528.

For example if I filter a worksheet and the list box and if I try to either spin up or down the rows spin buttons don't function.

Does filtering worksheet and the list box disable the movement of the spin buttons within the filtered rows at least?

[HansV]

Indeed, filtering the data doesn't go together with the code for the spin buttons.

[adam]

Does this mean that the rows would not be either spin up or down with filtered sheets?

[HansV]

The code only works correctly for a contiguous range, not for a filtered range consisting of mutiple areas.

If you sort the data by City Name and filter a specific city (for example Odessa), the code will work, since the filtered rows are all in a single area. But if you filter on a column that is not sorted, the code will fail.