
答案1
我最終創建了一個用戶表單,其中包含一些文字方塊和一個按鈕,用於將值保存在另一個名為「資料」的工作表中的同一列/行中。
像這樣的事情:
Dim xml As String
xml = xml + "<CellDetails>"
xml = xml + " <Budget>" + UserForm1.txtBudget.Text + "</Budget>"
xml = xml + " <Comments>" + UserForm1.txtComments.Text + "</Comments>"
xml = xml + " <StartDate>" + Format(MonthView1.Value, "yyyy-mm-dd") + "</StartDate>"
xml = xml + " <EndDate>" + Format(MonthView2.Value, "yyyy-mm-dd") + "</EndDate>"
xml = xml + "</CellDetails>"
ThisWorkbook.Sheets("Data").Range(Selection.Address).Value = xml
然後,我在單元格的“右鍵單擊”上使用事件列表器從工作表中觸發此操作,並使用值填充表單控制項。
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh 作為對象,ByVal 目標作為範圍,取消作為布林值)
出錯時繼續下一步
' 僅在按下 Ctrl 鍵時觸發此操作 If IsControlKeyDown() = True then
If Not ThisWorkbook.Sheets("Data").Range(Selection.Address).Value = "" Then
Dim XDoc As MSXML2.DOMDocument
Set XDoc = CreateObject("MSXML2.DOMDocument")
XDoc.LoadXML (ThisWorkbook.Sheets("Data").Range(Selection.Address).Value)
' Setting the form values
UserForm1.txtBudget.Text = XDoc.SelectSingleNode("//CellDetails/Budget").Text
UserForm1.txtComments.Text = XDoc.SelectSingleNode("//CellDetails/Comments").Text
' Setting the dates
UserForm1.MonthView1.Value = CDate(XDoc.SelectSingleNode("//CellDetails/StartDate").Text)
UserForm1.lbStartDate = XDoc.SelectSingleNode("//CellDetails/StartDate").Text
UserForm1.MonthView2.Value = CDate(XDoc.SelectSingleNode("//CellDetails/EndDate").Text)
UserForm1.lbEndDate = XDoc.SelectSingleNode("//CellDetails/EndDate").Text
End If
UserForm1.Show
Cancel = True
End If
On Error GoTo 0
結束子