Hi all,

I'm new to the advanced features of Excel and have run into a fe
problems on a project I'm working on.

What I have:

A clip-art image with a macro that will call a calendar when the imag
is clicked on.

The way it works:

the active cell is filled in with the selected date

What I want:

A macro that will point the output of the calendar date to a specifi
cell. And if that cell has any data in it at all, seek out the nex
available cell below it and enter the data there instead.

What I have tried:

I tried to write a macro that would make the active cell a particula
cell when the clipart image was clicked. This didn't work at all, bu
probably because I'm a newb. If I can get this to work, I can the
tackle trying to make the macro check to see if the field is empty o
not, and pointing to the next cell below if it is not empty.

Misc info: The list of dates entered should not exceed 20 entries o
so, so I don't think it will be a problem with excel spending a lon
time seeking the next empty field.


Thank you for any help you can give! THis has been driving me nuts

--
Ouk
-----------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...fo&userid=2398
View this thread: http://www.excelforum.com/showthread.php?threadid=37607

Hi Ouka

If you use this example the calendar will popup when you loop
through the cells.

http://www.rondebruin.nl/calendar.htm

Clicking on it will put the date in the activecell

--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ouka" wrote in message
...

Hi all,

I'm new to the advanced features of Excel and have run into a few
problems on a project I'm working on.

What I have:

A clip-art image with a macro that will call a calendar when the image
is clicked on.

The way it works:

the active cell is filled in with the selected date

What I want:

A macro that will point the output of the calendar date to a specific
cell. And if that cell has any data in it at all, seek out the next
available cell below it and enter the data there instead.

What I have tried:

I tried to write a macro that would make the active cell a particular
cell when the clipart image was clicked. This didn't work at all, but
probably because I'm a newb. If I can get this to work, I can then
tackle trying to make the macro check to see if the field is empty or
not, and pointing to the next cell below if it is not empty.

Misc info: The list of dates entered should not exceed 20 entries or
so, so I don't think it will be a problem with excel spending a long
time seeking the next empty field.


Thank you for any help you can give! THis has been driving me nuts!


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=376075

Hi Ron,

Thanks for your reply. I do like what part of that macro does and I'll
be incorperating it into mine, but it's not quite what I'm looking for.

I need the user to click on a static icon that will insert the selected
date into the next available field.

What I have currently is:

Private Sub Calendar1_Click()
Range("B65536").End(x1Up).Offset(1, 0).Select
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub

What this does is insert the selected date into the next available cell
in column B, from B2 to B65536 (I'm not really understanding why this
starts at B2 and not B1!)

Also, I don't understand is if I change

Range("B65536").End(x1Up).Offset(1, 0).Select

to

Range("B8:B58").End(x1Up).Offset(1, 0).Select

Why does the value still start at B2, go up to B8, then go back up to
B3 and stay on B3?

i.e. I click on my button with the calendar macro, and a date is filled
into B2. Click again, and it fills B3 .... up to B8. Click again after
that and it re-fills B3. And if I keep clicking, it keeps overwriting
B3.

I must really not understand the syntax because to me this statment
says this macro is to be applied over the range B8 to B58.

I have also tried substituting that Range command with:

Range("B8").Activate

Which inserts the date into B8 just fine. Problem is I've had no luck
in writing an If/Then/Else statement to check if the active field is
Null, and if not, move down a cell, recheck, etc until an empty cell is
found.


My next probelm is going to be ever worse. Corellating the dates
entered here into a calendar laid out in another workbook. Not looking
forward to that at all. But baby steps, baby steps. Have a lot to do
before I get to that point ~_^


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=376075

Hi Ouka

What this does is insert the selected date into the next available cell
in column B, from B2 to B65536 (I'm not really understanding why this
starts at B2 and not B1!)

It will find the first cell with data and if there is no data it is Cell B1
Range("B65536").End(x1Up)

It go one cell down
..Offset(1, 0).Select

Try this for a range

Sub testing2()
On Error Resume Next
Range("B8:B58").Cells.SpecialCells(xlCellTypeBlank s).Cells(1).Value = "Hi"
If Err.Number 0 Then
MsgBox "all cells are used"
Err.Clear
End If
On Error GoTo 0
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Ouka" wrote in message
...

Hi Ron,

Thanks for your reply. I do like what part of that macro does and I'll
be incorperating it into mine, but it's not quite what I'm looking for.

I need the user to click on a static icon that will insert the selected
date into the next available field.

What I have currently is:

Private Sub Calendar1_Click()
Range("B65536").End(x1Up).Offset(1, 0).Select
ActiveCell.Value = Calendar1.Value
Unload Me
End Sub

What this does is insert the selected date into the next available cell
in column B, from B2 to B65536 (I'm not really understanding why this
starts at B2 and not B1!)

Also, I don't understand is if I change

Range("B65536").End(x1Up).Offset(1, 0).Select

to

Range("B8:B58").End(x1Up).Offset(1, 0).Select

Why does the value still start at B2, go up to B8, then go back up to
B3 and stay on B3?

i.e. I click on my button with the calendar macro, and a date is filled
into B2. Click again, and it fills B3 .... up to B8. Click again after
that and it re-fills B3. And if I keep clicking, it keeps overwriting
B3.

I must really not understand the syntax because to me this statment
says this macro is to be applied over the range B8 to B58.

I have also tried substituting that Range command with:

Range("B8").Activate

Which inserts the date into B8 just fine. Problem is I've had no luck
in writing an If/Then/Else statement to check if the active field is
Null, and if not, move down a cell, recheck, etc until an empty cell is
found.


My next probelm is going to be ever worse. Corellating the dates
entered here into a calendar laid out in another workbook. Not looking
forward to that at all. But baby steps, baby steps. Have a lot to do
before I get to that point ~_^


--
Ouka
------------------------------------------------------------------------
Ouka's Profile: http://www.excelforum.com/member.php...o&userid=23988
View this thread: http://www.excelforum.com/showthread...hreadid=376075