Using the calendar control

[Todd_M]

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.

[Mudface]

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

[Russell Hauf]

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 

[Todd_M]

[Todd_M]

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.

[Russell Hauf]

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 ]

[Russell Hauf]

On 2002-02-25 17:15, Russell Hauf wrote:

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
...

[Todd_M]

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?