Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: Using the calendar control

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi:
    Im currently wiring a number of userforms so that data can be inserted to cells on a worksheet(The worksheet contains(column"d")dates from "1Jan01 to 31Dec2020) First objective is to have the user select a date from the calander control. Upon selection, a new userform appears w/ the date that was selected, in a listbox. Then the user has to enter a number($307.25 or what ever)into a text box on that same userform. The user presses a commandbutton named "OK" and I want that number pasted on a cell to the right of the date cell in the worksheet!
    My problem is that im not sure how to write the code for the commandbutton() to find the date on the worksheet from the listbox date and at the same time insert the number($307.25 or what ever)into the cell just to the right(column"e") of that date on the worksheet.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You don't really need to store the date selected in a listbox, does the following help?

    Private Sub CommandButton1_Click()

    For Each c In Range("D1:D100") ' Adjust your range if necessary
    If c.Value = ActiveSheet.Calendar1.Value Then
    c.Offset(0, 1).Value = TextBox2.Text
    Me.Hide
    Exit Sub
    End If
    Next c

    End Sub

  3. #3
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Todd,

    After speaking to you via email, I think this should work for you...let me know if it does not.

    -Russell


    Option Explicit


    Private Sub CommandButton1_Click()

    Dim intRow As Integer
    Dim intCol As Integer

    intCol = 3 ' column C, where your dates are
    intRow = 2 ' change this to the row where your dates start

    ' This first DO loop goes until it finds an empty column...
    ' this way if you enter more (or less) dates in the future,
    ' you don't have to change your code...
    ' if it's possible for the user to select a date that is
    ' not in your spreadsheet, then you should account for this
    ' in your code.
    Do While Trim(Cells(intRow, intCol).Text) <> ""
    If Cells(intRow, intCol).Value = Calendar1.Value Then
    ' You've found a matching date - now find the first
    ' cell to the right that is empty.
    intCol = intCol + 1
    Do While Trim(Cells(intRow, intCol).Text) <> ""
    intCol = intCol + 1
    Loop
    Cells(intRow, intCol) = TextBox2.Text
    Exit Do
    End If
    intRow = intRow + 1
    Loop

    ' Me.Hide or whatever here

    End Sub


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Russell-
    Thankyou for your time: I understand most of the code you have given me. As stated before via e-mail, I wrote that the code that mudface sent me and it does work with a few changes to fit my strategy. Here it is:

    Private Sub CommandButton3_Click()
    For Each c In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If c.Value = DateForm.Calendar2.Value Then
    c.Offset(0, 6).Value = TextBox1.Text
    Me.Hide
    Exit Sub
    End If
    Next c
    End Sub

    Notice the "offset(0, 6).value" I changed this so that my text from textbox1 will be inserted 6 cells to the right of the date column. Using the same formula, what can I add in the formula to identify if the 6th cell is full, and if then, how can I get the textbox1.value into the next empty cell to the right?
    Thanks again.

  6. #6
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-25 17:09, Todd_M wrote:
    Russell-
    Thankyou for your time: I understand most of the code you have given me. As stated before via e-mail, I wrote that the code that mudface sent me and it does work with a few changes to fit my strategy. Here it is:

    Private Sub CommandButton3_Click()
    For Each c In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If c.Value = DateForm.Calendar2.Value Then
    c.Offset(0, 6).Value = TextBox1.Text
    Me.Hide
    Exit Sub
    End If
    Next c
    End Sub

    Notice the "offset(0, 6).value" I changed this so that my text from textbox1 will be inserted 6 cells to the right of the date column. Using the same formula, what can I add in the formula to identify if the 6th cell is full, and if then, how can I get the textbox1.value into the next empty cell to the right?
    Thanks again.

    Private Sub CommandButton3_Click()
    For Each c In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If c.Value = DateForm.Calendar2.Value Then
    Do While c.Offset(0,1).text <> ""
    c = c.offset(0,1)
    Loop
    c.Offset(0, 1).Value = TextBox1.Text
    Me.Hide
    Exit Sub
    End If
    Next c
    End Sub

    [ This Message was edited by: Russell Hauf on 2002-02-25 17:16 ]

  7. #7
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,607
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-25 17:15, Russell Hauf wrote:
    On 2002-02-25 17:09, Todd_M wrote:
    Russell-
    Thankyou for your time: I understand most of the code you have given me. As stated before via e-mail, I wrote that the code that mudface sent me and it does work with a few changes to fit my strategy. Here it is:

    Private Sub CommandButton3_Click()
    For Each c In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If c.Value = DateForm.Calendar2.Value Then
    c.Offset(0, 6).Value = TextBox1.Text
    Me.Hide
    Exit Sub
    End If
    Next c
    End Sub

    Notice the "offset(0, 6).value" I changed this so that my text from textbox1 will be inserted 6 cells to the right of the date column. Using the same formula, what can I add in the formula to identify if the 6th cell is full, and if then, how can I get the textbox1.value into the next empty cell to the right?
    Thanks again.

    Private Sub CommandButton3_Click()
    For Each c In Worksheets("BALANCE SHEET").Range("D1:D10000") ' Adjust your range if necessary
    If c.Value = DateForm.Calendar2.Value Then
    Do While c.Offset(0,1).text <> ""
    c = c.offset(0,1)
    Loop
    c.Offset(0, 1).Value = TextBox1.Text
    Me.Hide
    Exit Sub
    End If
    Next c
    End Sub

    [ This Message was edited by: Russell Hauf on 2002-02-25 17:16 ]
    And you could also do this:
    ...
    If c.Value = DateForm.Calendar2.Value Then
    c.End(xlToRight).Offset(0, 1).Value = TextBox1.Text
    End If
    ...

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Russell- If I set the offset value to the 22nd column from the date column(column"D"), it reads "offset(0, 22). What do I set the other two offset numbers to:
    Do While c.Offset(0, 1).Text <> ""
    c = c.Offset(0, 1)
    So that if column 22 is not empy, then the textbox.value will go to column 23 or the next empty cell in that row?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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