How can assign macro that is in WorkSheet module and has several arguments to a command button for Insert new TableRow in protected Sheet?

Question

I have a Command button in my Worksheet ("Sheet1"), under a Table.

My sheet is protected and I need let user add new rows.

Bellow macro was wrote for do that: (This is in a WorkSheet module)

Sub TblNewLine(Sht As String, Tbl As String, Pass as String, Filtering As Boolean, Pivot As Boolean)
if Nor Pass = vbNullString then
    Dim strP as String
    strP = InputBox ("Please input Password","Password")
    if Not strP = Pass Then
        if not strP = vbNullString Then MsgBox "Wrong Password!", vbCritical, "Error!"
        Exit Sub
    End If
End If
    Sheets(Sht).Unprotect
    Sheets(Sht).ListObjects(Tbl).ListRows.Add
    Sheets(Sht).Protect AllowFiltering:=Filtering, AllowUsingPivotTables:=Pivot
End Sub 'TblNewLine

for example: arguments are:

sht:= "sheet1", Tbl:="PvtReport", Pass:="", Filtering:=True, Pivot:= True

Thus I need assign this macro to prepared command button that is under Table.

And passing arguments with pressing button.

In assign macro form, I cant find above macro in Macro names combo box.

How can I assign above macro and its related arguments to a command button?

Or is there any better solution for Insert new row to protected sheet Table?

Remove the `Private` keyword before `Sub`. See [this](https://superuser.com/questions/309524/how-do-i-use-a-private-sub-in-excel) for details. — Mrig, Jul 16 '17 at 05:52
Removed, but not shown in assign macro, yet. when I write my subroutine name in Macro name field in Assign macro, its called. but there were problem of passing arguments to my subroutine. — mgae2m, Jul 16 '17 at 06:09

Mrig · Accepted Answer · 2017-07-16T08:14:56.807

1

Try something like below:

Sub Button1_Click()
    Call TblNewLine("Sheet1", "PvtReport", True, True)
End Sub

Sub TblNewLine(Sht As String, Tbl As String, Filtering As Boolean, Pivot As Boolean)
    Sheets(Sht).Unprotect
    Sheets(Sht).ListObjects(Tbl).ListRows.Add
    Sheets(Sht).Protect AllowFiltering:=Filtering, AllowUsingPivotTables:=Pivot
End Sub

EDIT :

See image for reference

edited Jul 16 '17 at 08:14

answered Jul 16 '17 at 06:46

Mrig

11,612
2
13
27

Thanks. Seems there is no way to Pass arguments directly with a button. – mgae2m Jul 16 '17 at 06:48
How can I call `Buton1_Click` macro is there in `Sheet1` module by command button. Because `Button1_Click()` not found in Assign macro combo box. – mgae2m Jul 16 '17 at 06:56
You can simply write `Call Buton1_Click` but you should be able to see all sub names in `assign macro` list. Is it possible for you to share your workbook with dummy data so that I can have a look. – Mrig Jul 16 '17 at 07:04
Of course. My Workbook has many Persian characters. When I write arguments in `()` for subroutine, the sub name been hide from `assign macro` list. And subroutines are in `Worksheets` module not shown in `assign macro` list. – mgae2m Jul 16 '17 at 07:11
@MGae2M - Try in VBA editor choose `tools>options>editor format> font which support your language`. Though I am not sure if this will solve your problem. – Mrig Jul 16 '17 at 07:19
I'm ready to share my Workbook. Would you please invite me to a chat? – mgae2m Jul 16 '17 at 07:25
@MGae2M - You'll have to upload workbook online and share the link here. – Mrig Jul 16 '17 at 07:28
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/149314/discussion-between-mrig-and-mgae2m). – Mrig Jul 16 '17 at 07:34

mgae2m · Answer 2 · 2017-07-16T13:49:34.123

With great thanks from Mrig's guidance; For more explanation I Present whats i reached, as an answer:

there is a way for passing arguments with Command button, that I read in QA Collective's Answer for a related question.

At first i encountered 'cannot run Macro' error from Microsoft Excel, but when I closed and open workbooks document, looked Macro name field in Assign Macro was changed as bellow and my subroutine called truly by passing constants with click on button.

'FileName.xlsm'!'TblNewLine "SheetName", "tblReminder","",True,False'

Please not that ' character for enclose subroutine name and arguments list string, both in twisted '.

in above example, my sub needs 5 arguments so they pass with each command button that want calling this sub.

The point about assign macro that has no arguments, and there is in Worksheet module is there are listed below of Asign Macro combo list and not in order of subroutines are in other modules.

if subroutine macros has argument, there not listed in Asign Macro and should input them in related field as above example i explained.

How can assign macro that is in WorkSheet module and has several arguments to a command button for Insert new TableRow in protected Sheet?

2 Answers2