adding data to last row in table in different protected sheet

[pencari]

I am just learning with excel vba, Sorry For my bad english

i Have Struggle with running vba code to add Data To last Row In Each Table in each sheets when the sheets is being protected

i have this Code in This Workbook

Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
    wSheet.Protect Password:="Secret", _
    UserInterFaceOnly:=True
Next wSheet
End Sub

and this code to add the data, but it gets 1004 Error at the ".range(1) = MyValue"

Sub AddDataToTable()
Application.ScreenUpdating = False
Dim MyValue As String
Dim sh As Worksheet
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Set ws1 = Sheets("Setting")
Set ws2 = Sheets("R_Buy")
Set ws3 = Sheets("R_Sell")
Set ws4 = Sheets("S_Buy")
Set ws5 = Sheets("S_Sell")
Dim tbl As ListObject
Dim tb2 As ListObject
Dim tb3 As ListObject
Dim tb4 As ListObject
Dim tb5 As ListObject
Set tbl = ws1.ListObjects("T_Setting")
Set tb2 = ws2.ListObjects("T_R_Buy")
Set tb3 = ws3.ListObjects("T_R_Sell")
Set tb4 = ws4.ListObjects("T_S_Buy")
Set tb5 = ws5.ListObjects("T_S_Sell")
Dim newrow1 As ListRow
Dim newrow2 As ListRow
Dim newrow3 As ListRow
Dim newrow4 As ListRow
Dim newrow5 As ListRow

MyValue = InputBox("Add To Table, this cannot be undone")

'check if user clicked Cancel button and, if appropriate, execute statements
If StrPtr(MyValue) = 0 Then
'display message box confirming that user clicked Cancel button
MsgBox "You clicked the Cancel button"
'check if user entered no input and, if appropriate, execute statements
ElseIf MyValue = "" Then
'display message box confirming that user entered no input
MsgBox "There is no Text Input"
Else

Set newrow1 = tbl.ListRows.Add
With newrow1
    **.Range(1) = MyValue**
End With

Set newrow2 = tb2.ListRows.Add
With newrow2
    .Range(1) = MyValue
End With

Set newrow3 = tb3.ListRows.Add
With newrow3
    .Range(1) = MyValue
End With

Set newrow4 = tb4.ListRows.Add
With newrow4
    .Range(1) = MyValue
End With

Set newrow5 = tb5.ListRows.Add
With newrow5
    .Range(1) = MyValue
End With

End If
Application.ScreenUpdating = True

End Sub

can anybody help me with this or maybe have another ways to add data

 

[Belleke]

Hi,

Sub belle()
Dim MyValue As String, iRow As Long, wSheet As Worksheet
Set ws1 = Sheets("Setting")
Set ws2 = Sheets("R_Buy")
Set ws3 = Sheets("R_Sell")
Set ws4 = Sheets("S_Buy")
Set ws5 = Sheets("S_Sell")
For Each wSheet In Worksheets
    wSheet.Unprotect Password:="Secret"
Next wSheet
Application.ScreenUpdating = False
    MyValue = InputBox("Add To Table, this cannot be undone")
    If StrPtr(MyValue) = 0 Then
        MsgBox "You clicked the Cancel button"
    ElseIf MyValue = "" Then
        MsgBox "There is no Text Input"
    Else
    iRow = ws1.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws1.Cells(iRow, 1).Value = MyValue
    iRow = ws2.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws2.Cells(iRow, 1).Value = MyValue
    iRow = ws3.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws3.Cells(iRow, 1).Value = MyValue
    iRow = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws4.Cells(iRow, 1).Value = MyValue
    iRow = ws5.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws5.Cells(iRow, 1).Value = MyValue
    End If
Application.ScreenUpdating = True
For Each wSheet In Worksheets
    wSheet.Protect Password:="Secret", UserInterFaceOnly:=True
Next wSheet
End Sub

 

[pencari]

Else
    iRow = ws1.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws1.Cells(iRow, 1).Value = MyValue
    iRow = ws2.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws2.Cells(iRow, 1).Value = MyValue
    iRow = ws3.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws3.Cells(iRow, 1).Value = MyValue
    iRow = ws4.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws4.Cells(iRow, 1).Value = MyValue
    iRow = ws5.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
        ws5.Cells(iRow, 1).Value = MyValue
   EndIf

thx for reply Belleke,

i have tested your code, but in that part, i tried to add to Table row, and my table is not same Row and Coulum..

can i use the code to search for Table name?

 

[Belleke]

Can you post a small example of your workbook? (with dummy data)