VBA Excel UserForm "Update" Current Record

bmp1965 · Jun 17th 2016

Hello,

Fairly new at VBA and I'm stuck!
I've created a form where an event can be entered.
I also want the user to be able to update the event, if needed.
I figured out how to search on the event and re-fill the form, but I'm unable to figure out the code to "update" the information when/if changed.
I've been trolling excel boards for days borrowing code and none of it seems to be working.

I've attached a representative example of the file.

Any & all help would be greatly appreciated!
Bette

jaslake · Jun 18th 2016

Re: VBA Excel UserForm "Update" Current Record

Hi Bette

Your UPDATE EVENT CODE button is firing other Code Change Events and you need to prevent those from firing. Updated Code is in the attached.

In Module3 add this line of Code...

Code

Public Flag         As Boolean   '<---Add this Line of Code
Sub Button2_Click()
   HOSTED_EVENTS.Show
End Sub

In Module HOSTED_EVENTS add the three indicated lines of Code to Private Sub UpdateEvent_Click...

Code

Private Sub UpdateEvent_Click()
   If Me.SearchEventName.ListIndex < 0 Then GoTo Fin
   Dim irow         As Long
   Dim ws           As Worksheet
   Set ws = Worksheets("DATA")
   If MsgBox("Update Data?", vbOKCancel, "Term Form") = vbCancel Then
      Exit Sub
   Else   '<---Add this Line of Code
      Flag = True   '<---Add this Line of Code
   End If
   'Find Tagged Row | Not Working?
   irow = Me.SearchEventName.ListIndex + 2
   With ws
      .Range("A" & irow) = EventDate.Value
      .Range("B" & irow) = EventDay.Text
      .Range("C" & irow) = EventMonth.Text
      .Range("D" & irow) = EventName.Text
      .Range("E" & irow) = EventType.Text
      .Range("F" & irow) = EventStatus.Text
      .Range("g" & irow) = EventLocation.Text
      .Range("h" & irow) = EventStart.Value
      .Range("i" & irow) = EventFinish.Value
      .Range("j" & irow) = EventGuests.Value
      .Range("k" & irow) = EventIndiansContact.Text
      .Range("l" & irow) = EventSalesLead.Text
      .Range("m" & irow) = EventNotes.Text
   End With
   MsgBox ("Data Updated")
   Flag = False   '<---Add this Line of Code
   Exit Sub
Fin:
   MsgBox "There is nothing to copy"
   Unload Me
End Sub

Display More

Then in Private Sub SearchEventName_Change() add the indicated Line of Code...

Code

[Private Sub SearchEventName_Change()
   Dim ws           As Worksheet
   Dim lastrow      As Long
   Dim C            As Range
   If Flag = True Then Exit Sub  '<---Add this Line of Code
   Set ws = Worksheets("DATA")
   With ws

bmp1965 · Jun 21st 2016

Re: VBA Excel UserForm "Update" Current Record

jaslake,
You are my hero! Those modifications worked beautifully!
Thank you very much!!
Bette

jaslake · Jun 21st 2016

Re: VBA Excel UserForm "Update" Current Record

Hi Bette

You're welcome...glad I could help.

VBA Excel UserForm "Update" Current Record

Participate now!

Share