Please help! VBA: Replacing values in a sheet

[mpvmvp]

Ok, so I have a sheet that takes data from this form and adds it to a list of items. Sometimes the status of items change and the form is used to update the list. however, the form just enters a duplicate copy of the item without removing the old one...how can I do this? here is my code:

ActiveWorkbook.Sheets("Camera Report Form").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If

Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 3) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value

The list is sorted by the value "txtName.Value"
I tried entering this code:

If Selection.Value = txtName.Value Then
ActiveCell.ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents
End If

but that didn't seem to work...can anyone help??? Thanks so much

 

[XLGibbs]

I had this same problem with a form. Try unloading the form when is deactivated, and reloading it when you need to make a change


use the syntax

Unload Yourformname

Load Yourformname

 

[mpvmvp]

?

I'm not exactly sure what you mean...you're talking to someone who has experince with editing code in general, but doesn't really understand the basics of VBA

where do I put the code to unload adn reload the form?
what will unloding and reloading the form do?
what code do I use to delete the old value?
Thanks!

 

[XLGibbs]

You said you have a form that updates items in the list....

How is it that the form is originally shown on the screen for the user?

 

[mpvmvp]

ok, the form comes up and says to enter
a camera number
a department
a ranking
a status

and then puts the data into the first empty row
data 1 | data 2 | data 3 | data 4

and then I have a function to sort it my the first column...

so at the end if have duplicate rows when I put the same values into the form (as in the status changes)

 

[XLGibbs]

Perhaps I misunderstood...the list of items populates the form? or the list is only updated by the form?

If there is a button on the form that you use to confirm the update can you post that code?

It sounds like the form is merely creating a new line item rather than updating the existing list.

 

[mpvmvp]

There is just a button I hit to submit the form, you hit the nail on the head saying:

"It sounds like the form is merely creating a new line item rather than updating the existing list."

that's exactly what it does, it goes through each cell seeing if its empty, then offsets the active cell down one, and on and on till it gets to the bottom of the list and then it adds the entry.

At the end of my code I have it all sorted numerically.

what I want to do is, as it goes down line by line looking for an empty row (cell), if it hits one that matches the one that's just been entered into the form to delete the 4 cells (in a row) of data and replace them with the 4 that have just been submitted...

or just to delete the 4 old cells and place the new info at the bottom, since it all gets sorted anyways

 

[XLGibbs]

That is a little more complicated, can you post the existing code that is in place now?

 

[mpvmvp]

Here's the whole thing...


Private Sub cmdCancel_Click()
Unload Me
End Sub

Private Sub cmdClearForm_Click()
Call UserForm_Initialize
End Sub

Private Sub cmdOK_Click()
ActiveWorkbook.Sheets("Camera Report Form").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
If ActiveCell.Value = txtName.Value Then
ActiveCell.ClearContents
ActiveCell.Offset(0, 1).ClearContents
ActiveCell.Offset(0, 2).ClearContents
ActiveCell.Offset(0, 3).ClearContents
End If


Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 3) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value
If chkLunch = True Then
cboCourse.Value = "BROKEN"
ActiveCell.Offset(0, 1) = cboCourse.Value
Else
ActiveCell.Offset(0, 1) = cboCourse.Value
End If

Columns("A:C").Select
With Selection
.HorizontalAlignment = xlCenter
End With

Range("A7").Select
Range("A7:E500").Sort Key1:=Range("A500"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:D").Select
With Selection.font
.Name = "Tahoma"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("A7").Select
ActiveWorkbook.Sheets("Other").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 3) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value

If chkLunch = True Then
cboCourse.Value = "BROKEN"
ActiveCell.Offset(0, 1) = cboCourse.Value
Else
ActiveCell.Offset(0, 1) = cboCourse.Value
End If

If cboCourse.Value = "1" Then
ActiveCell.Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 3
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 3
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 3
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 3
Selection.font.Bold = True
End If
If cboCourse.Value = "2" Then
ActiveCell.Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 9
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 9
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 9
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 9
Selection.font.Bold = True
End If

If cboCourse.Value = "3" Then
ActiveCell.Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 6
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 6
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 6
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 6
Selection.font.Bold = True
End If

If cboCourse.Value = "4" Then
ActiveCell.Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 10
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 10
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 10
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 10
Selection.font.Bold = True
End If

If cboCourse.Value = "5" Then
ActiveCell.Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 4
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 4
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 4
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 1
Selection.Interior.ColorIndex = 4
Selection.font.Bold = True
End If
If cboCourse.Value = "BROKEN" Then
ActiveCell.Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 2
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True
End If
Range("A7").Select
Range("A6:D500").Sort Key1:=Range("A6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A6:D500").Sort Key1:=Range("B6"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:B").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Columns("A:D").Select
With Selection.font
.Name = "Tahoma"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("A7").Select

ActiveWorkbook.Sheets("Broken").Activate
Range("A7").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

If chkLunch = True Then
ActiveCell.Offset(0, 1).Value = "BROKEN"
ActiveCell.Value = txtName.Value
ActiveCell.Offset(0, 3) = txtPhone.Value
ActiveCell.Offset(0, 2) = cboDepartment.Value

If chkpriority = True Then
ActiveCell.Select
Selection.font.ColorIndex = 6
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 6
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 6
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True

ActiveCell.Offset(0, 1).Select
Selection.font.ColorIndex = 6
Selection.Interior.ColorIndex = 1
Selection.font.Bold = True
End If

End If

Range("A7").Select
Columns("A:C").Select
With Selection
.HorizontalAlignment = xlCenter
End With

Range("A7").Select
Range("A6:E500").Sort Key1:=Range("A500"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("A:D").Select
With Selection.font
.Name = "Tahoma"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("A7").Select
ActiveWorkbook.Sheets("Camera Report Form").Activate
Range("A7").Select
End Sub


Private Sub UserForm_Initialize()
txtName.Value = ""
txtPhone.Value = ""
With cboDepartment
.AddItem "OK"
.AddItem "Blurry"
.AddItem "Wavy"
.AddItem "Fuzzy"
.AddItem "Limited Mobility"
.AddItem "Flickering"
.AddItem "Other"
.AddItem "Totally Black"
End With
cboDepartment.Value = ""
With cboCourse
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
End With
cboCourse.Value = ""
txtName.SetFocus
End Sub
Private Sub chkLunch_Click()

If chkLunch.Value = True Then
cboCourse.Enabled = False
Else
cboCourse.Enabled = True
End If
End Sub

 

[mpvmvp]

The bold is what I need help with really... I might have it completly wrong, but I think that you might be able to get the idea of what I want to do...