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


--
Many Thanks

Sue

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

--
Regards,
Tom Ogilvy

"Sue" wrote:

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


--
Many Thanks

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



--
Many Thanks

Sue


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"Sue" wrote:

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


--
Many Thanks

Sue

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.


"Sue" wrote:

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


--
Many Thanks

Sue