VBA coding needed to create a search and replace userform

LoneWolf3574 · Dec 22, 2012

My problem – I have a userform that my coworkers and I use to enter data into a spreadsheet. The spreadsheet has been locked due to tinkering in the past by a couple coworkers that ultimately resulted in a large loss of data, taking an enormous amount of time to rebuild. On occasion, one of us enters the data incorrectly and it needs to be corrected. I am currently the only one with access to the passwords needed to go in and correct these errors.

What I would like – I would like to take the userform I have and create a search form where all that is needed is to enter the date and time you are searching for. After clicking the Search Command button, the textboxes would be populated and editable. After editing the desired textbox and clicking the Replace Command button, the userform would replace the found data row with the new/corrected data row. Whether the date and time is a single combobox or two doesn’t matter to me, but I have read that the single might be easier.

This is my coding for the data entry userform

Code:

Option Explicit

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Thermals")

'find  first empty row in database
iRow = Worksheets("Thermals").Range("P376").End(xlUp).Row + 1
 
'check for a temperature
If Trim(Me.cboDate.Value) = "" Then
  Me.cboDate.SetFocus
  MsgBox "Please enter the Date and Time, then the Temperatures"
  Exit Sub
End If

'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.cboDate.Value
  .Cells(iRow, 3).Value = Me.cboTime.Value
  .Cells(iRow, 4).Value = Me.txtChWtrSup.Value
  .Cells(iRow, 5).Value = Me.txtChWtrRet.Value
  .Cells(iRow, 6).Value = Me.txtConWtrRet.Value
  .Cells(iRow, 7).Value = Me.txtConWtrSup.Value
  .Cells(iRow, 8).Value = Me.txtHeatWtrSup.Value
  .Cells(iRow, 9).Value = Me.txtHeatWtrRet.Value
  .Cells(iRow, 10).Value = Me.txtSluHeatSup.Value
  .Cells(iRow, 11).Value = Me.txtSluHeatRet.Value
  .Cells(iRow, 12).Value = Me.txtWstHeatSup.Value
  .Cells(iRow, 13).Value = Me.txtWstHeatRet.Value
  .Cells(iRow, 14).Value = Me.txtDomHWtrRet.Value
  .Cells(iRow, 15).Value = Me.txtDomColdWtr.Value
  .Cells(iRow, 16).Value = Me.txtDomHWtrSup.Value
  .Cells(iRow, 17).Value = Environ("Username")
  .Cells(iRow, 18).Value = Now
End With

'clear the data
Me.cboDate.Value = ""
Me.cboTime.Value = ""
Me.txtChWtrSup.Value = ""
Me.txtChWtrRet.Value = ""
Me.txtConWtrRet.Value = ""
Me.txtConWtrSup.Value = ""
Me.txtHeatWtrSup.Value = ""
Me.txtHeatWtrRet.Value = ""
Me.txtSluHeatSup.Value = ""
Me.txtSluHeatRet.Value = ""
Me.txtWstHeatSup.Value = ""
Me.txtWstHeatRet.Value = ""
Me.txtDomHWtrRet.Value = ""
Me.txtDomColdWtr.Value = ""
Me.txtDomHWtrSup.Value = ""
Me.cboDate.SetFocus

End Sub

Private Sub UserForm_Initialize()
'Populate the Date & Time Comboboxes
  Dim rngDate As Range
  Dim rngTime As Range
  Dim ws As Worksheet
  Set ws = Worksheets("DateTime")
  For Each rngDate In ws.Range("DateList")
    Me.cboDate.AddItem rngDate.Text
  Next rngDate
  Set ws = Worksheets("DateTime")
  For Each rngTime In ws.Range("TimeList")
    Me.cboTime.AddItem rngTime.Text
  Next rngTime
    
End Sub

Private Sub cmdClose_Click()
  Sheets("Thermals").Select
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the 'Close' button!"
  End If
End Sub

This is my coding so far for the data search form, it's not much currently as it is my stumbling block

Code:

Option Explicit

Private Sub cmdSearch_Click()

End Sub

Private Sub cmdReplace_Click()

End Sub

Private Sub UserForm_Initialize()
'Populate the Date & Time Comboboxes
  Dim rngDate As Range
  Dim rngTime As Range
  Dim ws As Worksheet
  Set ws = Worksheets("DateTime")
  For Each rngDate In ws.Range("DateList")
    Me.cboDate.AddItem rngDate.Text
  Next rngDate
  Set ws = Worksheets("DateTime")
  For Each rngTime In ws.Range("TimeList")
    Me.cboTime.AddItem rngTime.Text
  Next rngTime
    
End Sub

Private Sub cmdClose_Click()
  Sheets("Thermals").Select
  Unload Me
  
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the 'Close' button!"
  End If
End Sub

If anybody could please help me with this I would appreciate it greatly as it is driving me a bit buggy. Thank you all in advance.

Andrew Poulsom · Dec 22, 2012

Welcome to MrExcel.

If you add the date and time to a single ComboBox then the ComboBox's ListIndex + 1 will give you the index into DateList of the selected item.

LoneWolf3574 · Dec 22, 2012

I'm still relatively new with VBA, so forgive me if I don't fully comprehend as I have only learned some of VBA that is relevant to my project so far. What I've done so far, if I understood correctly, is set the 2 columns (date and time) as a single list with the following in the "Name Manager"

Name - ThermalList
Refers to - =OFFSET(Thermals!$B$5:$C$376, 0, 0, COUNTA(Thermals!$B:$C)-1,1)

In the properties for the related combobox, I set the column count to 2

However, I can get the first column (dates in column B) to show up, but not the second (times in column C). If it helps any, each row has a date and time set at 2 hour increments as the data is entered (up to 372 rows of data).

Andrew Poulsom · Dec 22, 2012

Your named formula is only returning one column:

=OFFSET(Thermals!$B$5:$C$376, 0, 0, COUNTA(Thermals!$B:$C)-1,1)

LoneWolf3574 · Dec 24, 2012

I apologize for taking a couple of days getting back, holiday week and all. I've corrected the named formula so that it is now:

=OFFSET(Thermals!$B$5:$C$376, 0, 0, COUNTA(Thermals!$B:$C)-1,2)

I've played around with it some more, but the combobox keeps returning each column as a row in the combobox so that it now has alternating dates and times in the combobox. If I change the VBA coding for initializing the form to the following -

Code:

Private Sub UserForm_Initialize()
With cboDateTime
.ColumnCount = 2
.List = Range("DateTimeList").Value
End With

End Sub

It gives me the date and time in a single line, but the date is mm/dd/yy instead of the dd-mmm-yy I have formated on the sheet and the time is a number like 8.33333333333 instead of hh:mm

LoneWolf3574 · Dec 25, 2012

Pardon me, I meant to show

=OFFSET(Thermals!$B$5,0,0,COUNTA(Thermals!$B:$B)-1,2)

Much "tighter", not as large of a gap between the date and funky time. But the date and time displayed are still an issue.

Andrew Poulsom · Dec 25, 2012

Try it this way:

Code:

Private Sub UserForm_Initialize()
    Dim r As Long
    cboDateTime.ColumnCount = 2
    With Range("DateTimeList")
        For r = 1 To .Rows.Count
            cboDateTime.AddItem .Cells(r, 1).Text
            cboDateTime.Column(1, cboDateTime.ListCount - 1) = .Cells(r, 2).Text
        Next r
    End With
End Sub

LoneWolf3574 · Dec 25, 2012

That worked perfectly. Now when I get the rest of my search and replace problem taken care of I'll be dancing an Irish Jig.

VBA coding needed to create a search and replace userform

LoneWolf3574

New Member

Excel Facts

Andrew Poulsom

MrExcel MVP

LoneWolf3574

New Member

Andrew Poulsom

MrExcel MVP

LoneWolf3574

New Member

LoneWolf3574

New Member

Andrew Poulsom

MrExcel MVP

LoneWolf3574

New Member

Similar threads

Forum statistics

Share this page

VBA coding needed to create a search and replace userform

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock