Excel Userform - search and update existing row

0
votes

I have a userform that works great for inputting new data into my spreadsheet. I would like to modify this, so that the "Update" button would save data to the same row without duplicating. I would also like to make the "Search" to have some sort of drop-down list of values already in the sheet in row A1. I am still learning VBA, my code is probably a mess, and I don't know where to start. This is very much a work in progress- Any help, suggestions, ideas, etc. would be appreciated!

Current userform:

Private Sub CloseForm_Click()
'Close UserForm.
    Unload Me
End Sub

Private Sub EnterOutage_Click()
Sheets("Outages and Switching").Select
Range("A1").Select
Do Until ActiveCell.value = ""
ActiveCell.Offset(0, 0).Select
Loop
ActiveCell.Offset(1, 1).value = Me.REQ_Rev1.value
ActiveCell.Offset(1, 2).value = Me.SOS_Rev1.value
ActiveCell.Offset(1, 3).value = Me.OutageStart1.value
ActiveCell.Offset(1, 4).value = Me.OutageEnd1.value
ActiveCell.Offset(1, 5).value = Me.ConstRel.value
ActiveCell.Offset(1, 6).value = Me.Dispatch1.value
ActiveCell.Offset(1, 7).value = Me.OutageType1.value
ActiveCell.Offset(1, 8).value = Me.BPID1.value
ActiveCell.Offset(1, 9).value = Me.WorkOrder1.value
ActiveCell.Offset(1, 10).value = Me.Station_Line1.value
ActiveCell.Offset(1, 11).value = Me.Description.value
ActiveCell.Offset(1, 12).value = Me.Remarks1.value
ActiveCell.Offset(1, 13).value = Me.REQ_Link1.value
ActiveCell.Offset(1, 14).value = Me.SOS_Link1.value

End With
End Sub

Private Sub Image1_Click()
MyVal = "datFirstDay"
UserForm1.Show
End Sub

Private Sub CommandButton1_Click()

End Sub

Private Sub CommandButton2_Click()

End Sub

Private Sub CommandButton3_Click()

End Sub

Private Sub NewOutage_Click()

End Sub

Private Sub OutageEnd1_Enter()
OutageEnd1 = CalendarForm.GetDate
End Sub

Private Sub OutageStart1_Enter()
OutageStart1 = CalendarForm.GetDate
End Sub

Private Sub SaveOutage_Click()
'Changes start date to mm/dd/yyyy
 Dim dDate As Date
    'Sheet2.Range("D1").Value = dDate
Dim ws As Worksheet
    Dim addme As Long
    Set ws = Worksheets("Outages and Switching")
    addme = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
' Creates userform text input boxes
    With ws
        ws.Cells(addme, 1).value = Me.REQ_Rev1.value 'the number 1 here represents the Column A
        ws.Cells(addme, 2).value = Me.SOS_Rev1.value 'the number 2 here represents the Column B
        ws.Cells(addme, 7).value = Me.OutageStart1.value 'the number 7 represents Column G
        ws.Cells(addme, 8).value = Me.OutageEnd1.value 'the number 8 represents Column H
        ws.Cells(addme, 11).value = IIf(Me.ConstRel.value, 1, -1) 'the number 11 represents Column K, checkbox sends number instead of True/False
        ws.Cells(addme, 13).value = Me.Dispatch1.value 'the number 13 represents Column M
        ws.Cells(addme, 14).value = Me.OutageType1.value 'the number 14 represents Column N
        ws.Cells(addme, 15).value = Me.BPID1.value 'the number 15 represents Column O
        ws.Cells(addme, 16).value = Me.WorkOrder1.value 'the number 16 represents Column P
        ws.Cells(addme, 17).value = Me.Station_Line1.value 'the number 17 represents Column Q
        ws.Cells(addme, 22).value = Me.Description1.value 'the number 22 represents Column V
        ws.Cells(addme, 23).value = Me.Remarks1.value 'the number 23 represents Column W
        ws.Cells(addme, 24).value = Me.REQ_Link1.value 'the number 24 represents Column X
        ws.Cells(addme, 25).value = Me.SOS_Link1.value 'the number 25 represents Column O
    End With

'Reset text boxes code
'https://www.ozgrid.com/forum/forum/help-forums/excel-general/103251-how-to-clear-all-textboxes-and-checkbox-in-a-userform

        Dim oneControl As Object

For Each oneControl In OutageData.Controls
    Select Case TypeName(oneControl)
        Case "TextBox"
            oneControl.Text = vbNullString
        Case "CheckBox"
            oneControl.value = False
    End Select
Next oneControl


End Sub

Private Sub SearchOutage_Click()
Dim rng As Range
Dim i As Long
Dim lst As Range

If Me.REQ_Rev1.value = "" Then
MsgBox "Please enter a REQ number to find!", vbExclamation
Me.REQ_Rev1.SetFocus
Exit Sub
End If
With Sheets("Outages and Switching")
Set rng = .Range("A:A")
Set rng = rng.Find(What:=Me.REQ_Rev1.value, After:=.Range("A14"), LookAt:=xlWhole)
If rng Is Nothing Then
MsgBox "REQ not found!", vbExclamation
Me.REQ_Rev1.SetFocus
Exit Sub
End If

        Me.REQ_Rev1.value = rng.Offset(0, 0).value  'the number 0 here represents the Column A
        Me.SOS_Rev1.value = rng.Offset(0, 1).value  'the number 1 here represents the Column B
        Me.OutageStart1.value = rng.Offset(0, 6).value 'the number 6 represents Column G
        Me.OutageEnd1.value = rng.Offset(0, 7).value  'the number 7 represents Column H
        'IIf(Me.ConstRel.value, 1, -1) = rng.Offset(0, 11).value  'the number 11 represents Column K, checkbox sends number instead of True/False
        Me.Dispatch1.value = rng.Offset(0, 12).value  'the number 12 represents Column M
        Me.OutageType1.value = rng.Offset(0, 13).value  'the number 13 represents Column N
        Me.BPID1.value = rng.Offset(0, 14).value  'the number 14 represents Column O
        Me.WorkOrder1.value = rng.Offset(0, 15).value  'the number 15 represents Column P
        Me.Station_Line1.value = rng.Offset(0, 16).value  'the number 16 represents Column Q
        Me.Description1.value = rng.Offset(0, 23).value  'the number 23 represents Column V
        Me.Remarks1.value = rng.Offset(0, 24).value  'the number 24 represents Column W
        Me.REQ_Link1.value = rng.Offset(0, 25).value  'the number 25 represents Column X
        Me.SOS_Link1.value = rng.Offset(0, 26).value  'the number 26 represents Column O

End With
End Sub

Private Sub UpdateOutage_Click()

End Sub

' Forces user to use "Close" button instead of "X"
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button"
  End If
End Sub

I modified my code per the response from Reymond. It is closer to being functional, and now copies data back to the sheet. I also now have a userform to add a new "outage" row to the sheet, and another for "Update".

Here is the current flow for the 'Update':

  1. User opens "Update" userform, and inputs the REQ# in the 'REQ1' combobox

  2. User clicks "Search". This searches the sheet, and populates the userform with corresponding data

  3. User makes changes to fields, and selects "Update"

  4. Values in userform are loaded back into the sheet.

Here is the modified code:

Private Sub UserForm_Initialize()

' Disables all text fields except REQ# on UserForm Load
With Me
        .REQ_Rev1.Enabled = False
        .SOS1.Enabled = False
        .SOS_Rev1.Enabled = False
        .OutageStart1.Enabled = False
        .OutageEnd1.Enabled = False
        .ConstRel.Enabled = False
        .Dispatch1.Enabled = False
        .OutageType1.Enabled = False
        .BPID1.Enabled = False
        .WorkOrder1.Enabled = False
        .Station_Line1.Enabled = False
        .Device_Section1.Enabled = False
        .Description1.Enabled = False
        .Remarks1.Enabled = False
        .REQ_Link1.Enabled = False
        .SOS_Link1.Enabled = False
        .UpdateOutage1.Enabled = False
    End With


' >> Populate and sort ComboBox 1(REQ#) with data from 'Outages and Switching' sheet, column 1 <<
' https://www.tek-tips.com/viewthread.cfm?qid=1747506

Dim myRange As Range

    Set myRange = Sheets("Outages and Switching").Range("A15:A80")
    With Sheets("Outages and Switching").Sort
        .SortFields.Clear
        .SortFields.Add myRange, xlSortOnValues, xlAscending
        .SetRange myRange
        .Apply
    End With

    Me.REQ1.List = myRange.value

' >> Populate ComboBox 1(Dispatch) with data from 'Master List' sheet, column 1 <<

 ' Assign Variables

Dim Cl As Range

    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Master List").ListObjects("Table2").ListColumns(1).DataBodyRange
            If Not .Exists(Cl.value) Then .Add Cl.value, Nothing
        Next Cl
         Dispatch1.Clear
         Dispatch1.List = Application.Transpose(.keys)
         .RemoveAll
    End With

' >> Populate ComboBox 2(Outage Type) with data from 'Master List' sheet, column 2 <<
    With CreateObject("scripting.dictionary")
        For Each Cl In Sheets("Master List").ListObjects("Table2").ListColumns(2).DataBodyRange
            If Not .Exists(Cl.value) Then .Add Cl.value, Nothing
        Next Cl
         OutageType1.Clear
         OutageType1.List = Application.Transpose(.keys)
         .RemoveAll
    End With
End Sub

Private Sub Image1_Click()
MyVal = "datFirstDay"
UserForm1.Show
End Sub

Private Sub Search1_Click()
Dim rng As Range
Dim i As Long
Dim lst As Range

If Me.REQ1.value = "" Then
        MsgBox "Please enter an REQ number to find!", vbExclamation
        Me.REQ1.SetFocus
    Exit Sub
End If

With Sheets("Outages and Switching")
    Set rng = .Range("A:A")
    Set rng = rng.Find(What:=Me.REQ1.value, After:=.Range("A14"), LookAt:=xlWhole)
        If rng Is Nothing Then
            MsgBox "REQ not found!", vbExclamation
        Me.REQ1.SetFocus
    Exit Sub
End If
' Imports Outage values into UserForm based on REQ# search
        Me.REQ1.value = rng.Offset(0, 0).value  'the number 0 here represents the Column A
        Me.REQ_Rev1.value = rng.Offset(0, 1).value  'the number 0 here represents the Column A
        Me.SOS1.value = rng.Offset(0, 2).value  'the number 1 here represents the Column B
        Me.SOS_Rev1.value = rng.Offset(0, 3).value  'the number 1 here represents the Column B
        Me.OutageStart1.value = rng.Offset(0, 8).value 'the number 6 represents Column G
        Me.OutageEnd1.value = rng.Offset(0, 9).value  'the number 7 represents Column H
        'Me.ConstRel.value(1, -1) = rng.Offset(0, 13).value  'the number 11 represents Column K, checkbox sends number instead of True/False
        Me.Dispatch1.value = rng.Offset(0, 14).value  'the number 12 represents Column M
        Me.OutageType1.value = rng.Offset(0, 15).value  'the number 13 represents Column N
        Me.BPID1.value = rng.Offset(0, 16).value  'the number 14 represents Column O
        Me.WorkOrder1.value = rng.Offset(0, 17).value  'the number 15 represents Column P
        Me.Station_Line1.value = rng.Offset(0, 18).value  'the number 16 represents Column Q
        Me.Device_Section1.value = rng.Offset(0, 19).value  'the number 17 represents Column R
        Me.Description1.value = rng.Offset(0, 25).value  'the number 23 represents Column V
        Me.Remarks1.value = rng.Offset(0, 26).value  'the number 24 represents Column W
        Me.REQ_Link1.value = rng.Offset(0, 27).value  'the number 25 represents Column X
        Me.SOS_Link1.value = rng.Offset(0, 28).value  'the number 26 represents Column O

End With

With Me 'Enables text boxes after search is performed
        .REQ_Rev1.Enabled = .REQ1.value <> vbNullString
        .SOS1.Enabled = .REQ1.value <> vbNullString
        .SOS_Rev1.Enabled = .REQ1.value <> vbNullString
        .OutageStart1.Enabled = .REQ1.value <> vbNullString
        .OutageEnd1.Enabled = .REQ1.value <> vbNullString
        .ConstRel.Enabled = .REQ1.value <> vbNullString
        .Dispatch1.Enabled = .REQ1.value <> vbNullString
        .OutageType1.Enabled = .REQ1.value <> vbNullString
        .BPID1.Enabled = .REQ1.value <> vbNullString
        .WorkOrder1.Enabled = .REQ1.value <> vbNullString
        .Station_Line1.Enabled = .REQ1.value <> vbNullString
        .Device_Section1.Enabled = .REQ1.value <> vbNullString
        .Description1.Enabled = .REQ1.value <> vbNullString
        .Remarks1.Enabled = .REQ1.value <> vbNullString
        .REQ_Link1.Enabled = .REQ1.value <> vbNullString
        .SOS_Link1.Enabled = .REQ1.value <> vbNullString
        .UpdateOutage1.Enabled = .REQ1.value <> vbNullString
    End With

End Sub

Private Sub UpdateOutage1_Click()
Dim rng As Range
Dim i As Long
Dim lst As Range

With Sheets("Outages and Switching")
    Set rng = .Range("A:A")
    Set rng = rng.Find(What:=Me.REQ1.value, After:=.Range("A14"), LookAt:=xlWhole)
        If rng Is Nothing Then
            MsgBox "REQ not found!", vbExclamation
        Me.REQ1.SetFocus
    Exit Sub
End If

        rng.Offset(0, 0).value = Me.REQ1.value  'the number 0 here represents the Column A
        rng.Offset(0, 1).value = Me.REQ_Rev1.value  'the number 0 here represents the Column A
        rng.Offset(0, 2).value = Me.SOS1.value  'the number 1 here represents the Column B
        rng.Offset(0, 3).value = Me.SOS_Rev1.value  'the number 1 here represents the Column B
        rng.Offset(0, 6).value = Me.OutageStart1.value 'the number 6 represents Column G
        rng.Offset(0, 7).value = Me.OutageEnd1.value  'the number 7 represents Column H
        'rng.Offset(0, 11).value = Me.ConstRel.value(1, -1) 'the number 11 represents Column K, checkbox sends number instead of True/False
        rng.Offset(0, 12).value = Me.Dispatch1.value  'the number 12 represents Column M
        rng.Offset(0, 13).value = Me.OutageType1.value  'the number 13 represents Column N
        rng.Offset(0, 14).value = Me.BPID1.value  'the number 14 represents Column O
        rng.Offset(0, 15).value = Me.WorkOrder1.value 'the number 15 represents Column P
        rng.Offset(0, 16).value = Me.Station_Line1.value 'the number 16 represents Column Q
        rng.Offset(0, 17).value = Me.Device_Section1.value 'the number 17 represents Column R
        rng.Offset(0, 23).value = Me.Description1.value 'the number 23 represents Column V
        rng.Offset(0, 24).value = Me.Remarks1.value 'the number 24 represents Column W
        rng.Offset(0, 25).value = Me.REQ_Link1.value 'the number 25 represents Column X
        rng.Offset(0, 26).value = Me.SOS_Link1.value  'the number 26 represents Column O'

End With
End Sub

Private Sub OutageEnd1_Enter()
OutageEnd1 = CalendarForm.GetDate
End Sub

Private Sub OutageStart1_Enter()
OutageStart1 = CalendarForm.GetDate
End Sub



Private Sub CloseForm_Click()
'Close UserForm.
    Unload Me
End Sub

' Forces user to use "Close" button instead of "X"
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
    Cancel = True
    MsgBox "Please use the Close Form button"
  End If

End Sub

And here is a screenshot of the userform:

Update Outage UserForm

There is one thing I am struggling with now:

  1. How do I load the status of the 'ConstRel' checkbox into the userform, and copy it back to the sheet upon update? It is the value that is currently commented out, as I can't seem to figure out the syntax. If I don't include the "1" and "-1" it displays as TRUE/FALSE on my sheet, instead of YES/NO. I have tried variations of the following with no success so far:

    (Me.ConstRel.value , 1, -1) = rng.Offset(0, 10)

Thanks again for the assistance and patience as I fumble my way through VBA!

1
excelvbauserform

1 Answers

1
votes

Hope this code helps you. Try to reverse the code.

Cells(TextBox1.Text, 2).Value = TextBox2.Text

to update

TextBox2.Text = Cells(TextBox1.Text, 2).Value