PDA

View Full Version : [SOLVED] Select row based on multiple textbox values



Maxicus
02-19-2018, 02:51 AM
21646

I am working on a user-form to which will add data to a sheet (in this example sheet2) and then can be recalled and deleted if needed.

What i have done so far:
able to add new entries from the textboxes to sheet2.
able to populate the listbox with the data on sheet2.

What I can't seem to get to work and need to do:
if you click on one of the items within the listbox, to repopulate the 4 textboxes with that information. and
find the row that matches the 4 textboxes and delete the data on the sheet when pressing the delete button.

I have also set up a dymanic name range on the sheet called "People". if that helps. the dynamic name range is from A2 to D6

werafa
02-25-2018, 01:28 PM
here is an example where I reprogram the entries in a listbox.
see if this helps you ID how to attempt it.


Private Sub SetSummaryInfo()'write summary list entries
Dim myListBox As Object
Dim myForm As UserForm
Dim myRow As Long, lampCount As Long, sumWatts As Long, lastRow As Long
Dim myArray() As String
Dim myString As String
Dim mySheet As Worksheet
Dim myRange1 As Range, myRange2 As Range, myRange3 As Range


Set mySheet = ThisWorkbook.Worksheets("AssessmentDbase")
lastRow = mySheet.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set myForm = frmAssessment
Set myListBox = myForm.lbxSummaryInfo
myArray = GetFittingsList()

' set dashboard summary
Call ClearFixtureList
Call ListFixtures(myArray)

' set form fixtures summary
myListBox.Clear
For myRow = 1 To UBound(myArray, 2) 'add entries to listbox
myString = Trim(myArray(2, myRow) & " of " & myArray(1, myRow))
If myString <> "0 of W" Then myListBox.AddItem myString
Next myRow

'total count of fittings
lampCount = Application.WorksheetFunction.Sum(mySheet.Range("N2:N" & lastRow))
myForm.tbxSummaryCount = lampCount & " lamps"

'sum total of installed watts
Set myRange1 = mySheet.Range("I2:I" & lastRow) 'lamp count
Set myRange2 = mySheet.Range("J2:J" & lastRow) 'lamp watts
Set myRange3 = mySheet.Range("N2:N" & lastRow) 'fitting count
For myRow = 1 To lastRow - 1
sumWatts = sumWatts + myRange1.Cells(myRow).Value * myRange2.Cells(myRow).Value * myRange3.Cells(myRow).Value
Next myRow
sumWatts = sumWatts / 1000
myForm.tbxSummaryWatts = sumWatts & " kW Installed Load"





End Sub

and


Public Sub FormatAsDollars(ByRef myTextbox As MSForms.TextBox)Dim myVal As Double


myVal = ParseNumbers(myTextbox.Value)
myTextbox.Value = Format(myVal, "$#,##0.00")


End Sub