Many thanks for the code Dave

Yep sometimes I need to clear the cell! I have saved your code as i
will, no doubt, be usefull another time!

I am opting to put a messagebox in front of your other piece of code.
If I forewarn the user this should be enough protection!!! Anyway
they are the ones who have to type it back in <g

Regards

Kenn

--
N E Bod
-----------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...fo&userid=1126
View this thread: http://www.excelforum.com/showthread.php?threadid=27006

Maybe adjusting the first version:

Private Sub CommandButton2_Click()
If myCell Is Nothing Then
'do nothing
Else
myCell.Value = Me.TextBox1.Value
Me.TextBox1.Value = ""
Me.ListBox1.List = myRng.Value
End If
End Sub

to

Private Sub CommandButton2_Click()
If myCell Is Nothing Then
'do nothing
Else
myCell.Value = Me.TextBox1.Value
Me.TextBox1.Value = ""
Me.ListBox1.List = myRng.Value
'break it for the next click!
set mycell = nothing
End If
End Sub

N E Body wrote:

Many thanks for the code Dave

Yep sometimes I need to clear the cell! I have saved your code as it
will, no doubt, be usefull another time!

I am opting to put a messagebox in front of your other piece of code.
If I forewarn the user this should be enough protection!!! Anyway,
they are the ones who have to type it back in <g

Regards

Kenny

--
N E Body
------------------------------------------------------------------------
N E Body's Profile: http://www.excelforum.com/member.php...o&userid=11263
View this thread: http://www.excelforum.com/showthread...hreadid=270067

--

Dave Peterson