Hello there,
I am chancing my arm here to ask if I could possibly get some assistance with setting up a spinbutton on a userform I am building to allow me to "cycle" through a subset of rows on a worksheet that have previously been "searched" for using another command button? (I am using Excel 2010)
To clarify, I have a single worksheet with about 30 columns of data (Computer Asset Inventory) that have common and unique values amoungst the columns.
I have so far been able to build a userform with a mixture of text boxes and combo boxes that do lookups using a lookup worksheet with some success, and I have also been able to build the beginnings of a "Search" command button that will let me search on a single field (column) to display the first row that matches from the same worksheet. But due to setting up some dummy data I know that I only get the first result of say three rows that could possibly match - what I'd like to do (if it is possible) is to have a spinbutton on the same userform so that I can "cycle" through the other two records (rows) - and if I could see the number of results and the relative record I was on that would be amazing!
I realise that I may be asking a lot here, but I've got most of it working, but the spinbutton and search bit is stumping me a bit - I've not done any VBA before two days ago, so have picked up what I can by reading and YouTubing!
Happy to start simple to understand the syntax and then build it out from there, or I can give full examples of what I have so far, whatever works for you.
Thanks very much for any advice you may be able to offer.
Currently, my "Search" button does this - it looks at single field at present called "Hostname on Guest"
Private Sub CommandButton1_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("VMGuests").Range("F" & row_number)
If item_in_review = txtHostNameOnGuest.Text Then
txtDateAudited = Sheets("VMGuests").Range("A" & row_number)
cboAuditedBy = Sheets("VMGuests").Range("B" & row_number)
cboState = Sheets("VMGuests").Range("C" & row_number)
cboHost = Sheets("VMGuests").Range("D" & row_number)
End If
Loop Until item_in_review = ""
End Sub
This works quite well so far - but I know that the record it shows me (based on what I see in the DateAudited, AuditedBy, State and Host fields) is the first one of three possible matches in my worksheet called VMGuests.
Thanks again.
Kind regards,
James
I am chancing my arm here to ask if I could possibly get some assistance with setting up a spinbutton on a userform I am building to allow me to "cycle" through a subset of rows on a worksheet that have previously been "searched" for using another command button? (I am using Excel 2010)
To clarify, I have a single worksheet with about 30 columns of data (Computer Asset Inventory) that have common and unique values amoungst the columns.
I have so far been able to build a userform with a mixture of text boxes and combo boxes that do lookups using a lookup worksheet with some success, and I have also been able to build the beginnings of a "Search" command button that will let me search on a single field (column) to display the first row that matches from the same worksheet. But due to setting up some dummy data I know that I only get the first result of say three rows that could possibly match - what I'd like to do (if it is possible) is to have a spinbutton on the same userform so that I can "cycle" through the other two records (rows) - and if I could see the number of results and the relative record I was on that would be amazing!
I realise that I may be asking a lot here, but I've got most of it working, but the spinbutton and search bit is stumping me a bit - I've not done any VBA before two days ago, so have picked up what I can by reading and YouTubing!
Happy to start simple to understand the syntax and then build it out from there, or I can give full examples of what I have so far, whatever works for you.
Thanks very much for any advice you may be able to offer.
Currently, my "Search" button does this - it looks at single field at present called "Hostname on Guest"
Private Sub CommandButton1_Click()
row_number = 0
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("VMGuests").Range("F" & row_number)
If item_in_review = txtHostNameOnGuest.Text Then
txtDateAudited = Sheets("VMGuests").Range("A" & row_number)
cboAuditedBy = Sheets("VMGuests").Range("B" & row_number)
cboState = Sheets("VMGuests").Range("C" & row_number)
cboHost = Sheets("VMGuests").Range("D" & row_number)
End If
Loop Until item_in_review = ""
End Sub
This works quite well so far - but I know that the record it shows me (based on what I see in the DateAudited, AuditedBy, State and Host fields) is the first one of three possible matches in my worksheet called VMGuests.
Thanks again.
Kind regards,
James
