Hi guys,

I am trying to make a cell in a column populate when i select the third
option from a drop down box within another field. I have been trying to
use an If statement but with little success. Basically when the user
selects Close from a drop down box the current system time will
populate in the time closed column.

Any help would be greatly appreciated,

Aidan Mullane


--
afmullane
------------------------------------------------------------------------
afmullane's Profile: http://www.excelforum.com/member.php...o&userid=33567
View this thread: http://www.excelforum.com/showthread...hreadid=534314

This is one way to do it. I'm assuming you want the cell to move down a
row each time to keep track? If not, then you can just take out the
Do/Loop and the second If/Then statement.

If cboList.Value = "Close" Then
rCell = 3 'This is the row number
cCell = 7 'This is the column number
Do
If Cells(rCell, cCell).Value < "" Then
rCell = rCell + 1
End If
Loop Until Cells(rCell, cCell).Value = ""
Cells(rCell, cCell).Value = "=Now()"
Cells(rCell, cCell).Activate
ActiveCell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
End If

Where "cboList" is whatever you named your ComboBox.
Hope that helps.

-Ikaabod

afmullane Wrote:
Hi guys,

I am trying to make a cell in a column populate when i select the third
option from a drop down box within another field. I have been trying to
use an If statement but with little success. Basically when the user
selects Close from a drop down box the current system time will
populate in the time closed column.

Any help would be greatly appreciated,

Aidan Mullane


--
Ikaabod
------------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371
View this thread: http://www.excelforum.com/showthread...hreadid=534314

Cheers for that man but it dosn't seem to work. I'm getting an error at
the if statement.

Any more help would be greatly appreciated,

Aidan


--
afmullane
------------------------------------------------------------------------
afmullane's Profile: http://www.excelforum.com/member.php...o&userid=33567
View this thread: http://www.excelforum.com/showthread...hreadid=534314

I assumed by "drop down box" you meant a combobox. Whatever you name
that combobox you will have to change "cboList" to that name.

For example: the default name for a combobox is "ComboBox1" so jus
replace cboList with ComboBox1. The easiest way would be to tur
"Design Mode" on using the Macro Toolbar and then double click th
combobox. Then insert the code between "Private Sub name()" and "En
Sub". It would look like this if the combobox was called "ComboBox1":

Private Sub ComboBox1_Change()
If ComboBox1.Value = "Close" Then
rCell = 3 'This is the row number
cCell = 7 'This is the column number
Do
If Cells(rCell, cCell).Value < "" Then
rCell = rCell + 1
End If
Loop Until Cells(rCell, cCell).Value = ""
Cells(rCell, cCell).Value = "=Now()"
Cells(rCell, cCell).Activate
ActiveCell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
End If
End Sub


afmullane Wrote:
Cheers for that man but it dosn't seem to work. I'm getting an error a
the if statement.

Any more help would be greatly appreciated,

Aida

--
Ikaabo
-----------------------------------------------------------------------
Ikaabod's Profile: http://www.excelforum.com/member.php...fo&userid=3337
View this thread: http://www.excelforum.com/showthread.php?threadid=53431

i'm not quite sure how you have created the combobox but i've simply
created it through a seperate sheet. i'm not sure if i have to create
it another way???


--
afmullane
------------------------------------------------------------------------
afmullane's Profile: http://www.excelforum.com/member.php...o&userid=33567
View this thread: http://www.excelforum.com/showthread...hreadid=534314