gpt4 book ai didi

vba - 根据用户表单输入获取本周的星期一

转载 作者:行者123 更新时间:2023-12-04 20:30:45 24 4
gpt4 key购买 nike

我对 VBA 很陌生,所以请多多包涵。我有一个用户表单,它将最终创建一个 2 或 6 周的日程表。用户窗体有一个文本框,它会自动填充当前一周的星期一作为前瞻开始日期。如果用户输入日期,它将使用该日期作为前瞻开始日期。

我似乎无法弄清楚的部分是公式,因此当用户输入日期时,它会计算该周的星期一。

编码:

Private Sub UserForm_Initialize()
'Inserts date of Monday this week into "Start Date" field in UserForm
LookAheadDate1.Value = Date - Weekday(Date) + 2
End Sub

Private Sub Generate_Click()

Dim StartDate As Date
Dim EndDate As Date
Dim ws As Worksheet
Dim addme As Long
Set ws = Worksheets("Projects")
addme = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


' Clears Look Ahead sheet - Row 5 and below
With Sheets("Look Ahead")
.Rows(5 & ":" & .Rows.Count).Delete
End With

'Force user to select either option button
If ((Me.OptionButton1.Value = 0) * (Me.OptionButton2.Value = 0)) Then
MsgBox "Please select 2 or 6 Week Look Ahead"
End If

'Force user to select either option button
If ((Me.OptionButton1.Value)) Then
ThisWorkbook.Worksheets("Look Ahead").Range("E6").Value = "2 Week Look Ahead"
End If

If ((Me.OptionButton2.Value)) Then
ThisWorkbook.Worksheets("Look Ahead").Range("E6").Value = "6 Week Look Ahead"
End If

' Set StartDate Variable - If Start Date field value is blank, use this weeks start date, otherwise use start date field value
If IsNull(Me.LookAheadDate1.Value) Or Me.LookAheadDate1.Value = "" Then
StartDate = Date
Else
StartDate = LookAheadDate1.Value
End If

' Option buttons / Code to create end date for 2 or 6 week look ahead
Dim res As Date
If OptionButton1.Value Then
EndDate = StartDate - Weekday(Date) + 16
ElseIf OptionButton2.Value Then
EndDate = StartDate - Weekday(Date) + 44
End If

'Write Look Ahead date range in cell "E7"

ThisWorkbook.Worksheets("Look Ahead").Range("E7").Value = StartDate - Weekday(Date) + 2 & " to " & EndDate

'Clear all fields in UserForm
Dim oneControl As Object
For Each oneControl In ProjectData.Controls
Select Case TypeName(oneControl)
Case "TextBox"
oneControl.Text = vbNullString
Case "CheckBox"
oneControl.Value = False
End Select
Next oneControl

'Close UserForm.
Unload Me

End Sub

Private Sub ToggleButton1_Click()

End Sub

Private Sub UserForm_Click()

End Sub

Private Sub LookAheadDate1_Change()

End Sub

Private Sub Cancel_Click()
'Close UserForm if "Cancel" Button is pressed
Unload Me
End Sub

'Checks for entry of valid date

Private Sub LookAheadDate1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If LookAheadDate1 = vbNullString Then Exit Sub

If IsDate(LookAheadDate1) Then
LookAheadDate1 = Format(LookAheadDate1, "Short Date")
Else
MsgBox "Please Enter Valid Date"
Cancel = True
End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'If the user clicks the "X" to close the UserForm, then cancel
If CloseMode = 0 Then Cancel_Click
End Sub

最佳答案

如果我正确理解您关于日期的问题,您希望编辑此代码块:

If IsNull(Me.LookAheadDate1.Value) Or Me.LookAheadDate1.Value = "" Then
StartDate = Date ' <--- this is where you want Monday, right?
Else
StartDate = LookAheadDate1.Value
End If
如果是这样,请将标记的行替换为
StartDate = Date - Application.WorksheetFunction.Weekday(Date,3)
获取本周星期一的日期为 StartDate .
  • Date返回当前日期(类似于 Now,但没有时间部分)
  • 使用Weekday ( =WEEKDAY() ) 获取星期一来自 this answerPaul

  • 编辑
    Else分支:
    Dim enteredDate as Date
    enteredDate = CDate(LookAheadDate1.Value)
    StartDate = enteredDate - Application.WorksheetFunction.Weekday(enteredDate,3)
    CDate根据当前语言环境从文本转换为日期。如果您需要更复杂的转换,您可能需要手动解析日期文本。
    我相信 LookAheadDate1是一个文本框,因为我看到您使用 Format(..., "Short Date") 给它一个值.如果是日期/时间选择器,您可能不需要 CDate称呼。

    关于vba - 根据用户表单输入获取本周的星期一,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51442507/

    24 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com