0

Hi Im having a problem on Updating and Deleting the data on my Worksheet. Using VBA Excel user form I always got a problem on Application defined object defined error.

I tried Rows select method but I cant get the output that I needed. 

Private Sub cmdUpdate_Click()

If Me.cmbAssetTag.Value = "" Then

MsgBox "Asset Tag Can Not be Blank!!!", vbExclamation, "Asset Tag"

Exit Sub

End If

AssetTag = Me.cmbAssetTag.Value

Sheets("Assets").Select

Dim r As Single

Dim msg As String

Dim ans As String

r = Me.cmbAssetTag.Value

Rows(r, Cells).Select

Cells(r, 2) = Me.txtName.Text

Cells(r, 3) = Me.txtManager.Text

Cells(r, 4) = Me.txtDepartment.Text

Cells(r, 5) = Me.txtAction.Text

Cells(r, 6) = Me.txtTemplate.Text

Cells(r, 7) = Me.txtModel.Text

Cells(r, 8) = Me.txtReason.Text

Cells(r, 9) = Me.txtDate.Text

Cells(r, 10) = Me.txtUpdatedBy.Text

msg = "Asset Tag" & rowselect & "  Successfully Updated...Continue?"

Unload Me

ans = MsgBox(msg, vbYesNo, "Update")

If ans = vbYes Then

UserForm1.Show

Else

Sheets("Assets").Select

End If

End Sub

The actual result should replaced the data on the sheet with the replaced input

riskypenguin
  • 2,139
  • 1
  • 10
  • 22
Hen Molina
  • 1
  • 2
  • Have you tried using Microsoft Powerapps? Check it out if you haven’t it’s really cool :-) – NickyTheWrench Sep 25 '19 at 03:40
  • Unless you've displayed the form in `vbModeless` (the default is `vbModal`), you will likely not be able to interact with the worksheet i.e., like `Select` or `Activate`. Also, [your code should usually not need to rely on those methods](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Almost never. So you should learn how to avoid those methods, and refactor accordingly. Cheers. – David Zemens Sep 25 '19 at 03:41
  • Also, `Rows(rowselect, Cells)` this looks like possibly invalid syntax. `Cells` refers to the entire worksheet -- every single cell, all of them, at once. `Rows(, )` seems like it would fail. You just use `Rows(rowselect)`. But, note the caveat in my comment above, you probably still cannot `Select` on that. – David Zemens Sep 25 '19 at 03:43
  • Also worth noting: `vbModeless` allows the user to interact with the environment while the form is displayed. This is disabled by default (`vbModal`) probably at least in part because allowing the user to modify the environment introduces a much greater risk that the user will alter the environment in a way that causes some runtime error. So, unless you're prepared to handle that additional complexity, it will be best to avoid using `Select`. – David Zemens Sep 25 '19 at 03:46
  • Guys Im only using VBA in excel, Can you help me on this one. I also set command button for it to update in one click then error happen on rows.select – Hen Molina Sep 25 '19 at 08:07
  • @HenMolina I would suggest you to `debug.print & "Cells : " & Cells & " r : " & r` the error might be there – TourEiffel Sep 25 '19 at 08:31
  • Where should I put that sir Dorian? I just started vba in excel so Im so confused using new lines – Hen Molina Sep 25 '19 at 08:37
  • @HenMolina Just before `rows(r,cells).select` If you don't have imediate windows you can use `msgBox & "Cells : " & Cells & " r : " & r` instead, it will populate a windows with the value of cells and r – TourEiffel Sep 25 '19 at 08:44
  • Run time error 7 Out of Memory sir – Hen Molina Sep 25 '19 at 09:04
  • @henmolina `msgBox "Cells : " & Cells & " r : " & r` idid a typo try this – TourEiffel Sep 25 '19 at 09:13

0 Answers0