Stop TextBox Clearing Cell

S

Sue

Hi All

Back again with another problem the following works great - however if I do
not fill in all of the Textboxes the empty Textboxes clears the info alrady
on the sheet is there anyway to stop this happening?

Private Sub SearchForValue()
Dim rngFound As Range
Dim rngToSearch As Range
Dim FindWhat As String
Dim Matches As Boolean

Set rngFound = Nothing
FindWhat = Me.TextBox21.Text
Do
Set rngToSearch = Worksheets(shIndex).Columns("A")
Set rngFound = rngToSearch.Find(What:=FindWhat, _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
MatchCase:=False)


If Not (rngFound Is Nothing) Then
With Me
..Tb2.Text = rngFound.Offset(0, 2).Value
..Tb3.Text = rngFound.Offset(0, 1).Value
..Tb4.Text = rngFound.Offset(0, 4).Value
rngFound.Offset(0, 5) = Tb5.Text '< data ready to be entered
rngFound.Offset(0, 6) = Tb6.Text '< data ready to be entered
rngFound.Offset(0, 11) = Tb11.Text ' < data already in the cell on the sheet
rngFound.Offset(0, 12) = Tb12.Text ' < data already in the cell on the sheet

' on entering Tb5 & Tb6 -- Tb11 & Tb12 the data already on the sheet clears
leaving empty cells - really need this not to happen'.

End With
Else
shIndex = shIndex + 1
End If
Loop Until Not rngFound Is Nothing Or shIndex > Worksheets.Count

TextBox21.SetFocus
End Sub
 
T

Tom Ogilvy

Since you are entering data in an existing data line, the way I would do it
is to populate the textboxes when the user makes the "discriminating" entry.
Then he/she can edit the ones they want and all are written back. The
alternative is to put in If statements and only write to the cell if that
specific textbox is not a null string.

if len(trim(tb11.text)) <> 0 then _
rngFound.Offset(0, 11) = Tb11.Text
 
J

John Bundy

not sure exactly what your doing but you can say only if that cell is blank,
something like this (untested)

if rngFound.Offset(0, 11) = "" then _
rngFound.Offset(0,11)="" else _
rngFound.Offset(0,11)=Tb11.Text

-John
Please rate when your question is answered to help us and others know what
is helpful.
 
S

Sue

Hi Tom

That was fast and furious -- thank you! -- used

if len(trim(tb11.text)) <> 0 then _
rngFound.Offset(0, 11) = Tb11.Text

and it worked perfectly 1st time of asking -- only got to do it to 40
Textboxes, but that is for tomorrow
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code does not seem to work, but it should. 2
Loop in Excel 3
Assistance required 4
Can't get the If and Else statement correct 3
Find ID Number 10
Find and loop 5
Value NOT being input 3
ID Number Col A 3

Top