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

<pre><font color='#000000'>
<font color='#000080'>Option</font> <font color='#000080'>Explicit</font>

<hr align=left width=500><font color='#000080'>Private</font> <font color='#000080'>Sub</font> CommandButton1_Click()

<font color='#000080'>Dim</font> intRow <font color='#000080'>As</font> <font color='#000080'>Integer</font>
<font color='#000080'>Dim</font> intCol <font color='#000080'>As</font> <font color='#000080'>Integer</font>

intCol = 3 <font color='#008000'>' column C, where your dates are</font>
intRow = 2 <font color='#008000'>' change this to the row where your dates start</font>

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

<font color='#008000'>' Me.Hide or whatever here</font>

<font color='#000080'>End</font> <font color='#000080'>Sub</font>

</font></pre>

 

[Todd_M]

[No message]

 

[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:

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

 

[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?