Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 65

Thread: Date Problems on VBA Forms

  1. #1
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location

    Date Problems on VBA Forms

    Hi there,

    I have built a Data Capture Form on Excel 2003 VBA forms. I have a calendar on the form but just need a couple of things to make it work.

    1. When a user opens the form, how can I get the calendar to automatically go to today's date?

    2. When the user clicks OK I need the date on the calendar to go into the first column on the excel spreadsheet.

    Can you help??

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    We cannot second guess what the form looks like or what you mean by ... go into the first column on the excel spreadsheet.

    Posting an examle workbook with your working code will help.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Here is the code I have used so far:

    [VBA]Private Sub Calendar1_Click()

    End Sub
    Private Sub cboAddRemove_Change()
    End Sub
    Private Sub cboDemand_Change()
    End Sub
    Private Sub cboSALT_Change()
    End Sub
    Private Sub cboValueCreation_Change()
    End Sub
    Private Sub cboValueFailure_Change()
    End Sub
    Private Sub cmdAdd_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("DemandData")
    'find first empty row in database
    iRow = ws.Cells(Rows.Count, 1) _
    .End(xlUp).Offset(1, 0).Row
    'check for a date
    If Trim(Me.txtDemandDate.Value) = "" Then
    Me.txtDemandDate.SetFocus
    MsgBox "Nothing has been added!! Please either fill out the boxes or close the form if not required."
    Exit Sub
    End If
    'copy the data to the database
    ws.Cells(iRow, 1).Value = Me.txtDemandDate.Value
    ws.Cells(iRow, 2).Value = Me.txtRoll.Value
    ws.Cells(iRow, 3).Value = Me.cboProduct.Value
    ws.Cells(iRow, 4).Value = Me.cboWhereFrom.Value
    ws.Cells(iRow, 5).Value = Me.cboDemand.Value
    ws.Cells(iRow, 6).Value = Me.cboTerm.Value
    ws.Cells(iRow, 7).Value = Me.cboIntFreq.Value
    ws.Cells(iRow, 8).Value = Me.txtOtherDemand
    ws.Cells(iRow, 9).Value = Me.txtResponse.Value
    ws.Cells(iRow, 10).Value = Me.cboAddRemove.Value
    ws.Cells(iRow, 11).Value = Me.txtWhereFromTo.Value
    ws.Cells(iRow, 12).Value = Me.txtPayInterestTo.Value
    ws.Cells(iRow, 13).Value = Me.cboSALT.Value
    ws.Cells(iRow, 14).Value = Me.cboWhatMatters.Value
    ws.Cells(iRow, 15).Value = Me.txtActDate.Value
    ws.Cells(iRow, 16).Value = Me.txtMatDate.Value
    ws.Cells(iRow, 17).Value = Me.cboDidWeDoWhatMatters.Value
    ws.Cells(iRow, 18).Value = Me.txtIfNotWhyNot.Value
    ws.Cells(iRow, 19).Value = Me.cboValueFailure.Value
    ws.Cells(iRow, 20).Value = Me.cboValueCreation.Value
    ws.Cells(iRow, 21).Value = Me.txtExtraDemand.Value

    'clear the data
    Me.txtDemandDate.Value = ""
    Me.txtRoll.Value = ""
    Me.cboProduct.Value = ""
    Me.cboWhereFrom.Value = ""
    Me.cboDemand.Value = ""
    Me.cboTerm.Value = ""
    Me.cboIntFreq.Value = ""
    Me.txtOtherDemand.Value = ""
    Me.cboAddRemove.Value = ""
    Me.txtWhereFromTo.Value = ""
    Me.txtPayInterestTo.Value = ""
    Me.cboSALT.Value = ""
    Me.txtResponse.Value = ""
    Me.cboWhatMatters.Value = ""
    Me.txtMatDate.Value = ""
    Me.txtActDate.Value = ""
    Me.cboDidWeDoWhatMatters.Value = ""
    Me.txtIfNotWhyNot.Value = ""
    Me.cboValueFailure.Value = ""
    Me.cboValueCreation.Value = ""
    Me.txtExtraDemand.Value = ""
    Me.txtDemandDate.SetFocus
    End Sub
    Private Sub cmdClose_Click()
    Unload Me
    End Sub
    Private Sub ComboBox1_Change()
    End Sub
    Private Sub Label15_Click()
    End Sub
    Private Sub txtDemandDate_Change()

    End Sub
    Private Sub txtPayInterestTo_Change()
    End Sub
    Private Sub txtSALT_Change()
    End Sub
    Private Sub txtWhatMatters_Change()
    End Sub
    Private Sub UserForm_Click()
    End Sub
    Private Sub UserForm_Initialize()
    txtDemandDate.Value = ""

    With cboDemand
    .AddItem " "

    .AddItem "I want to reinvest total."
    .AddItem "I want to reinvest my capital only."
    .AddItem "I want to reinvest some money."
    .AddItem "I want to close my account."
    .AddItem "I want something else."

    End With
    cboDemand.Value = ""



    With cboProduct

    .AddItem " "

    .AddItem "Guaranteed Reserve"

    .AddItem "FRISA"

    End With

    cboProduct.Value = ""


    With cboWhereFrom

    .AddItem " "

    .AddItem "Branch"

    .AddItem "Mat Form"

    .AddItem "Telephone"

    .AddItem "Letter"

    End With

    cboWhereFrom.Value = ""

    With cboTerm

    .AddItem " "

    .AddItem "6 months"

    .AddItem "1 year"

    .AddItem "2 years"

    .AddItem "3 years"

    .AddItem "4 years"

    End With

    cboTerm.Value = ""

    With cboIntFreq

    .AddItem " "

    .AddItem "On Maturity"

    .AddItem "Monthly"

    .AddItem "Annually"

    End With

    cboIntFreq.Value = ""

    With cboAddRemove

    .AddItem " "

    .AddItem "Add"

    .AddItem "Remove"

    End With

    cboAddRemove.Value = ""

    txtDemandDate.SetFocus

    With cboSALT

    .AddItem " "

    .AddItem "Yes"

    .AddItem "No"

    End With

    cboSALT.Value = ""

    With cboWhatMatters

    .AddItem " "

    .AddItem "On Time"

    .AddItem "ASAP"

    .AddItem "Now"

    End With

    cboWhatMatters.Value = ""

    With cboDidWeDoWhatMatters

    .AddItem " "

    .AddItem "Yes"

    .AddItem "No"

    End With

    cboDidWeDoWhatMatters.Value = ""

    With cboValueCreation

    .AddItem " "

    .AddItem "1 - Exceeds Expectations"

    .AddItem "2 - Meets Expectations"

    .AddItem "3 - Below Expectations"

    .AddItem "4 - Failed Security"

    End With

    cboValueCreation.Value = ""

    With cboValueFailure

    .AddItem " "

    .AddItem "V"

    .AddItem "F"

    .AddItem "W"

    .AddItem "WV"

    .AddItem "WF"

    End With

    cboValueFailure.Value = ""

    End Sub
    [/VBA]
    Not sure if this helps. I'm pretty much a Newbie with VBA as I haven't used it in about 10 years lol

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Post a workbook, with a form, I haven't the time to recreate what you have already done.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    There you go, mate
    Last edited by WillieC; 10-07-2008 at 03:55 AM.

  6. #6
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Problem sorted, XLD. Had a bit of a play around and it seems to be working now. Thanks for your help anyway.

    Oh just one other thing. At the minute, when the Data Capture Form is opened, Excel stays visible in the background. Is it possible to have it so that when the Form is opened Excel minimizes?

  7. #7
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    That's better!

    To get the calendar to start at today, add this to your form

    [vba]

    Private Sub UserForm_Activate()
    Me.Calendar1.Value = Date
    End Sub
    [/vba]

    The second question. Doesn't it already do that?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  8. #8
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Quote Originally Posted by xld
    That's better!

    To get the calendar to start at today, add this to your form

    [vba]

    Private Sub UserForm_Activate()
    Me.Calendar1.Value = Date
    End Sub
    [/vba]

    The second question. Doesn't it already do that?
    I have it now so that the Date goes into the cell I needed. I think there was a conflict as I had a textbox still in for the date.

  9. #9
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    When you select a date in the calendar, where should it go?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  10. #10
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Quote Originally Posted by xld
    When yu select a date in the calendar, where should it go?
    I have it sorted I think. It should go into Column A in the next available cell.

  11. #11
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    No I meant on the form, there are 3 date fields there.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  12. #12
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Quote Originally Posted by xld
    No I meant on the form, there are 3 date fields there.
    Originally I wanted it to go into the field that just said Date but I have removed that textbox now and the calendar has replaced it. The user doesn't have to worry about selecting a date now as the calendar does it for them automatically. The other date boxes will only be filled in the odd time but this must be done by the user.

  13. #13
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Anybody know how to make Excel disappear when a form opens?? At the minute it sits in the background.

    Thanks

  14. #14
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Application.Visible = False
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  15. #15
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Thanks.

  16. #16
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Just another quick question for XLD.

    I've noticed that when I open the Data Capture Form it allows the user to type things into the combo boxes instead of just being able to choose the selections that's there. Is there a way of having it so that nothing can be typed into the combo box?

    Also, is there a way that I can stop the users from changing the date on the calendar eg freezing the calendar on that day's date?

    Thanks again for all your help

  17. #17
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Change the style property of the combobox to dropdownlist.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  18. #18
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Thanks.

    Any ideas with regards to locking the calendar?

  19. #19
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Sorry, I missed that, what are you trying to do?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  20. #20
    VBAX Regular
    Joined
    Oct 2008
    Posts
    36
    Location
    Hi XLD,

    If you still have the file I sent, you'll see that there are two textboxes that say Mat Date and Act Date. Is is possible that when a user clicks on these that a calendar automatically "pops up" for them to select the date?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •