我在Internet上發(fā)現(xiàn)了很多資源，這些資源幾乎可以完成我想做的事情，但不完全是我的工作。我有一個(gè)命名范圍“ daylist”。對(duì)于dayList中的每一天，我想在用戶表單上創(chuàng)建一個(gè)按鈕來(lái)運(yùn)行當(dāng)天的宏。我能夠動(dòng)態(tài)添加按鈕，但不知道如何將daycell.text從命名范圍傳遞到按鈕，事件處理程序和宏：S這里，我必須創(chuàng)建用戶表單的代碼：Sub addLabel()ReadingsLauncher.Show vbModelessDim theLabel As ObjectDim labelCounter As LongDim daycell As RangeDim btn As CommandButtonDim btnCaption As StringFor Each daycell In Range("daylist") btnCaption = daycell.Text Set theLabel = ReadingsLauncher.Controls.Add("Forms.Label.1", btnCaption, True) With theLabel .Caption = btnCaption .Left = 10 .Width = 50 .Top = 20 * labelCounter End With Set btn = ReadingsLauncher.Controls.Add("Forms.CommandButton.1", "runButton", True) With btn .Caption = "Run Macro for " & btnCaption .Left = 80 .Width = 80 .Top = 20 * labelCounter ' .OnAction = "btnPressed" End With labelCounter = labelCounter + 1Next daycellEnd Sub為了解決上述問(wèn)題，我目前提示用戶輸入他們想運(yùn)行的日期（例如Day1）并將其傳遞給宏，然后它可以工作：Sub B45runJoinTransactionAndFMMS()loadDayNumber = InputBox("Please type the day you would like to load:", Title:="Enter Day", Default:="Day1")Call JoinTransactionAndFMMS(loadDayNumber)End SubSub JoinTransactionAndFMMS(loadDayNumber As String)xDayNumber = loadDayNumberSheets(xDayNumber).Activate-Do stuffEnd Sub因此，對(duì)于我的每個(gè)runButton，它都需要顯示daycell.text，并運(yùn)行一個(gè)使用相同文本作為參數(shù)的宏，以選擇要執(zhí)行其工作的工作表。任何幫助都是極好的。我見(jiàn)過(guò)響應(yīng)，它們動(dòng)態(tài)地編寫(xiě)了VBA代碼來(lái)處理宏，但是我相信一定可以通過(guò)傳遞參數(shù)來(lái)更好地完成它，只是不確定如何做到。提前謝謝了！
3 回答
哆啦的時(shí)光機(jī)
TA貢獻(xiàn)1779條經(jīng)驗(yàn) 獲得超6個(gè)贊
捕獲單擊工作表的示例。將其放在工作表模塊中：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' e.g., range(A1:E1) is clicked
If Not Application.Intersect(Target, Range("A1:E1")) Is Nothing Then
MsgBox "You clicked " & Target.Address
End If
End Sub
回首憶惘然
TA貢獻(xiàn)1847條經(jīng)驗(yàn) 獲得超11個(gè)贊
有用！測(cè)試我使用的代碼：（Sub intersectCallingMacro() Range("D8").Select 'used for testing only Set target = ActiveCell If Not Application.Intersect(target, Range("A1:M100")) Is Nothing Then Call testIntersect(ActiveCell.Value) End If End Sub Sub testIntersect(dayString As String) Dim xDayString As String xDayString = dayString Sheets(xDayString).Activate End Sub 為代碼格式化道歉）將單元值作為參數(shù)傳遞給宏。驚人！
添加回答
舉報(bào)
0/150
提交
取消