0

Using a userform, I have created an 'Update' function to the form to make amendments to certain rows of data. For some reason, when I make an amendment and click on the 'Update' button, the data from columns T, P and W are randomly added to Sheet1 instead of Sheet2.

Private Sub Update_Click()

Dim selectedRow As Long
'Make Sheet2 active
Sheet2.Activate

Dim x As Range
Set WS = Worksheets("Data")

selectedRow = Application.WorksheetFunction.Match(CLng(Me.SN.Value), WS.Range("A:A"), 0)
'Transfer information
Cells(selectedRow, 3).Value = hour.Value & ":" & minute.Value & " " & ampm.Value
Cells(selectedRow, 4).Value = PTID.Value
Cells(selectedRow, 2).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
Cells(selectedRow, 5).Value = UNIT.Value
Cells(selectedRow, 6).Value = PCBOX.Value
Cells(selectedRow, 7).Value = WASTE.Value
Cells(selectedRow, 8).Value = REPORTED.Value
Cells(selectedRow, 9).Value = DETBOX.Value
Cells(selectedRow, 10).Value = FOLBOX.Value
Cells(selectedRow, 11).Value = SUMBOX.Value
Cells(selectedRow, 12).Value = CAPBOX.Value
Cells(selectedRow, 13).Value = EHOR.Value
Cells(selectedRow, 14).Value = TECHS.Value & "," & TECHS2.Value & "," & TECHS3.Value & "," & TECHS4.Value
Cells(selectedRow, 15).Value = ERRORBOX.Value
Cells(selectedRow, 16).Value = PREVBOX.Value
Cells(selectedRow, 17).Value = SOP.Value
Cells(selectedRow, 18).Value = AUDIFILE.Value
Cells(selectedRow, 19).Value = INTERFILE.Value
Cells(selectedRow, 20).Value = cmbdate2.Value & "/" & cmbmonth2.Value & "/" & cmbyear2.Value
Cells(selectedRow, 23).Value = Phase.Value
Cells(selectedRow, 24).Value = QIM.Value
MsgBox "Entry updated, please check your entry.", , "Entry Update"

End Sub

The problem seems to occur with the lines for columns, 16, 20 and 23 (which refer to the PREVBOX, dates, and Phase values respectively. Attached below is an image of the stray cells being copied to Sheet1 cells highlighted in yellow (and effectively overwriting any cells on that sheet).

Stray data

The rows and columns also match the rows and columns of the data on Sheet2 but I'm not sure if the cells are being overwritten in Sheet2 which is the intention of the update button.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
ChrisH
  • 15
  • 4
  • 3
    You're writing to cells without qualifying which sheet to write to. Excel is guessing which sheet you want. Sometimes it guesses incorrectly. Change `Cells(selectedRow...` to `WS.Cells(selectedRow...` – CLR Sep 26 '22 at 07:01
  • @CLR it seems to have solved the issue, thank you! I'll keep a lookout if it happens again. Much appreciated – ChrisH Sep 26 '22 at 07:09
  • Obligatory post on avoiding select, activate, etc. [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Darren Bartrup-Cook Sep 26 '22 at 07:30

0 Answers0