marlonsaveri
Board Regular
- Joined
- Jan 28, 2011
- Messages
- 68
Hi, I wish one could told me how we move through a listbox using spinbutton, ie, when we click in the spin button, we go up or down the list box.
Private Sub SpinButton1_Change()
If SpinButton1.Value = 0 Then SpinButton1.Value = 10
ListBox1.ListIndex = ListBox1.ListCount - SpinButton1.Value
Label1.Caption = SpinButton1.Value
End Sub
Private Sub UserForm_Initialize()
ListBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
SpinButton1.Max = ListBox1.ListCount
SpinButton1.Min = 0
SpinButton1.Value = 10
End Sub
Private Sub SpinButton1_SpinDown()
If ListBox1.ListIndex > 0 Then
ListBox1.Selected(ListBox1.ListIndex - 1) = True
End If
End Sub
Private Sub SpinButton1_SpinUp()
If ListBox1.ListIndex + 1 < ListBox1.ListCount Then
ListBox1.Selected(ListBox1.ListIndex + 1) = True
End If
End Sub
Private Sub ListBox1_Change()
SpinButton1.Value = ListBox1.ListIndex
TextBox1.Text = TheValue(ListBox1.ListIndex, 2)
TextBox1.SetFocus
End Sub
Private Sub SpinButton1_Change()
If SpinButton1.Value > ListBox1.ListCount Then
SpinButton1.Value = ListBox1.ListCount
End If
If ListBox1.ListCount > SpinButton1.Value And SpinButton1.Value >= 0 And SpinButton1.Value <= ListBox1.ListCount Then
ListBox1.ListIndex = SpinButton1.Value
Label1.Caption = SpinButton1.Value
End If
End Sub
Dim MyMtx() As String
Private Sub SpinButton1_Change()
On Error GoTo trataerro1
If SpinButton1.Value > ListBox1.ListCount Then
SpinButton1.Value = ListBox1.ListCount
End If
If ListBox1.ListCount > SpinButton1.Value And SpinButton1.Value >= 0 And SpinButton1.Value <= ListBox1.ListCount Then
ListBox1.ListIndex = SpinButton1.Value
End If
TextBox1.SetFocus
trataerro1:
End Sub
Private Sub UserForm_Initialize()
On Error GoTo trataerro2
Dim j As Integer
Dim i As Integer
' Title of the form
Me.Caption = ActiveSheet.Name
' Listbox
final = Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row + Sheets("SampleName").Range("N" & Rows.Count).End(xlUp).Row - 2
ReDim MyMtx(final, 2) As String
For i = 0 To Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row - 2
MyMtx(i, 1) = Sheets("SampleName").Range("E2").Offset(i, 0)
Next i
j = 0
For i = Sheets("SampleName").Range("E" & Rows.Count).End(xlUp).Row - 1 To final - 1
MyMtx(i, 1) = Sheets("SampleName").Range("N2").Offset(j, 0)
j = j + 1
Next i
For i = 0 To final - 1
ListBox1.AddItem MyMtx(i, 1)
Next i
ListBox1.Selected(0) = True
trataerro2:
End Sub
Private Sub CommandButton1_Click()
On Error GoTo trataerro3
Dim valor As String
Dim busca As Range
Dim SearchedWord As String
Dim planilha As Worksheet
'Save values on the sheet
Set planilha = ActiveWorkbook.ActiveSheet
planilha.Range("A1").FormulaR1C1 = BoxCity.Text
'etc
For i = 0 To ListBox1.ListCount 'for each prop...
SearchedWord = MyMtx(i, 1) 'Find the prop on the sheet
Set busca = ActiveWorkbook.ActiveSheet.Cells.Find(what:=SearchedWord, after:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Not busca Is Nothing Then 'If something is found...
valor = Replace(MyMtx(i, 2), ".", ",") 'We correct commas and dots...
If Not valor = "" Then 'and paste on the sheet.
Cells(busca.Row, busca.Column + 3).FormulaR1C1 = valor
End If
End If
Next i
Unload AddProp
trataerro3:
End Sub
Private Sub ListBox1_Change()
On Error GoTo trataerro4
'Change the frame
Frame1.Caption = ListBox1.Text
'Correct spinbutton (it happens when we click on the listbox)
SpinButton1.Value = ListBox1.ListIndex
'Save the value on the array...
TextBox1.Text = MyMtx(ListBox1.ListIndex, 2)
'Get focus
TextBox1.SetFocus
trataerro4:
End Sub
Private Sub TextBox1_Change()
On Error Resume Next
MyMtx(ListBox1.ListIndex, 2) = TextBox1.Text
End Sub