Hello
Im new to VBA and i have search the forums on the net for about 1 month now and
i cant find the solution.
I have found this code and needs a little help to understand it.
1. Can someone explain to me why the spinbutton up dont work wheen i run the macro whit another sheet
then "ListHolder" open ?
2. Is it possible whit this code to have 2 sheets whit difrent information and controll it trough a combobox lets say if i chose sheet1
in the box, i can add delete the data in sheet1 and the same for sheet 2 ?
Tanx
Petter
[VBA]Option Explicit
Private Sub UserForm_Activate()
populateBox 'populates listbox on load
End Sub
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_item to your textbox name
If TextBox_Item.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("ListHolder")
'change all references to listbox_item to your listbox name
If ListBox_Items.Value <> "" Then
iRow = ListBox_Items.ListIndex + 2
.Rows(iRow).Insert Shift:=xlDown
Else
iRow = .UsedRange.Rows.Count + 1
End If
.Cells(iRow, 1).Value = TextBox_Item.Value
TextBox_Item.Value = ""
End With
populateBox 'repopulate listbox
End Sub
Private Sub CommandButton_Remove_Click()
Dim i As Long
'change listbox name and sheet location to yours
i = ListBox_Items.ListIndex + 1
With ThisWorkbook.Sheets("ListHolder")
.Rows.EntireRow(i + 1).Delete
End With
populateBox
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("ListHolder")
i = ListBox_Items.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) = ListBox_Items.Value
populateBox
ListBox_Items.Selected(i) = True
End If
End With
End Sub
Private Sub SpinButton1_SpinUp()
Dim i As Long
i = ListBox_Items.ListIndex + 1
If i > 1 And i < ActiveSheet.UsedRange.Rows.Count Then
With ThisWorkbook.Sheets("ListHolder")
.Cells(i + 1, 1).Value = .Cells(i, 1).Value
.Cells(i, 1) = ListBox_Items.Value
End With
populateBox
ListBox_Items.Selected(i - 2) = True
End If
End Sub
Public Sub populateBox()
ListBox_Items.Clear
Dim cell As Range
'change to location of your list
With ThisWorkbook.Sheets("ListHolder")
If .UsedRange.Rows.Count > 1 Then
For Each cell In .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, 1))
ListBox_Items.AddItem cell.Value
Next cell
End If
End With
End Sub
Private Sub CommandButton_Save_Click()
'optional; ensures changes to order of list are saved
ThisWorkbook.Save
Unload Me
End Sub
Private Sub CommandButton_Cancel_Click()
Unload Me
End Sub [/VBA]
Reply With Quote
