Cfrgtkky

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:

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!

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