Using the calendar control

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
 
Upvote 0
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>
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
...
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top