Spin Button Fun

[adam]

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.

I'm filtering by category as you have assumed. But I cant get it working.

[HansV]

Try changing the line

With Range("Data")

in the SpinUp and SpinDown code to

With Range("Data").SpecialCells(xlCellTypeVisible)

[adam]

Thanks Hans that does work.

I do have a Command Button in the form which when clicked removes the selected row from the list box. But it isn't working anymore. Is this because I have changed the codes according to the spin buttons?

Here's how it is,

Code: Select all

Private Sub CommandButton_Remove_Click()
    Dim i As Long
    'change listbox name and sheet location to yours
    i = ListBox2.ListIndex + 1
    With ThisWorkbook.Sheets("Dates")
        .Rows.EntireRow(i + 1).Delete
    End With
    MakeList
End Sub

[HansV]

Replace

ThisWorkbook.Sheets("Dates")

with

Range("Data").SpecialCells(xlCellTypeVisible)

[adam]

Thanks Hans. The code works better by committing the +1 in ListIndex+1 and with your suggestion. But If I try to remove the row that comes after the column headers in the worksheet I get an error message clearing the range (Data). Otherwise the code works fine with other rows.

What might be causing this?

[HansV]

By deleting the cell A4 used in the definition of Data, the range becomes invalid. Define Data instead as

=OFFSET(Dates!$A$4,1,0,COUNTA(Dates!$A:$A)-1,8)

[adam]

That helps. Thanks Hans.

[adam]

Following is a code which I’m trying to imply so that it would add data from seven textboxes & one combo box, to the sheet embedded in the code.

This code adds data to the row above the selected row from the list box with data from the seven textboxes and one combo box.

Code: Select all

Private Sub CommandButton_Add_Click()
    'this sub is for adding new items to the listbox
    Dim iRow As Long
    'Error Checking
    'change all references to textbox to your textbox name
    If txtCityName.Value = "" Then
        MsgBox "You forgot to enter an item", , "Error"
        Exit Sub
    End If
    'change line below to reflect location of your list
    With ThisWorkbook.Sheets("Dates")
        'change all references to ListBox2 to your listbox name
        If ListBox2.Value <> "" Then
            iRow = ListBox2.ListIndex + 2
            .Rows(iRow).Insert Shift:=xlDown
        Else
            iRow = .UsedRange.Rows.Count + 1
        End If
        .Cells(iRow, 1).Value = txtCityName.Value
        txtCityName.Value = ""
        .Cells(iRow, 2).Value = txtDept.Value
         txtDept.Value = ""
        .Cells(iRow, 3).Value = cboDept1.Value
        cboDept1.Value = ""
'and so on with rest of the text boxes named with column names
    End With
    MakeList 'repopulate listbox
End Sub

But when I press the add button my data not get added to the row above the selected row. What might have I done wrong here?

[HansV]

This is basically the same problem you had with the spin buttons, so take a look at the way I changed the code for the spin buttons to see what you have to modify here.

[adam]

Which spin button code post are you referring. Is that the spin button code at Post=18726 or at Post=18767

[HansV]

The most recent version - the one you're using now.

[adam]

This is basically the same problem you had with the spin buttons, so take a look at the way I changed the code for the spin buttons to see what you have to modify here.

Here is how I modified the code. This modification does allow me to add data to the row above the selected row in the list box. But I seem to have a text blinking effect with the code. I would be happy if you could comment on the code.

Code: Select all

Private Sub CommandButton_Add_Click()
    Dim i As Long
Dim iRow As Long
    Dim c As Long
    Dim varTemp As Variant
   With Range("Data").SpecialCells(xlCellTypeVisible)
        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
           ListBox2.Selected(i) = True
        End If
        If ListBox2.Value <> "" Then
            iRow = ListBox2.ListIndex + 1
            .Rows(iRow).Insert Shift:=xlDown
        Else
            iRow = .Rows.Count + 1
        End If
        .Cells(iRow, 1).Value = txtCityName.Value
        txtDate.Value = ""
        .Cells(iRow, 2).Value = txtDept.Value
         txtSerial.Value = ""
        .Cells(iRow, 3).Value = cboDept1.Value
        cboCode.Value = ""
    End With
MakeList
End Sub

[adam]

The part of the code that comes after End If

Code: Select all

        .Cells(iRow, 1).Value = txtCityName.Value
        txtCityName.Value = ""
        .Cells(iRow, 2).Value = txtDept.Value
         txtDept.Value = ""
        .Cells(iRow, 3).Value = cboDept1.Value
        cboDept1.Value = ""

[HansV]

You have added the code to move a row up. That was obviously not the intention! I just meant that you should replace

With ThisWorkbook.Sheets("Dates")

with

With Range("Data").SpecialCells(xlCellTypeVisible)

[adam]

Do I have to change the Error Checking message also with every for every textbox.
If txtCityName.Value = "" Then
MsgBox "You forgot to enter an item", , "Error"

With your suggestion the data now gets added to the row below instead of the row above. what might be the reason?

[HansV]

I've lost track of what you're doing. Could you post a recent version of your workbook.

[adam]

Attached please find the workbook

[HansV]

Since the code inserts a row too far down, change

iRow = ListBox2.ListIndex + 2

to

iRow = ListBox2.ListIndex + 1

Note for others reading this thread: all code posted by adam is from Use a spinbutton to change items in listbox and update sheet that populates listbox on VBA Express.

[adam]

Note for others reading this thread: all code posted by adam is from Use a spinbutton to change items in listbox and update sheet that populates listbox on VBA Express.

I don't believe that this is a correct statement.

Any way, the "add" button would not work if the sheet is filtered. Right? I mean after filtering lets say by city name if I want to add a data row would the add button be adjusted to work.

I tried but didn't work.

[HansV]

No, it won't work with filtered data.