please help! Replacing blank cells from a userform textbox

[aintlifegrand79]

I have created a database with two userforms the 1st userform enters the data
into the database, while the second finds data from the database based on a
project number and allows the user to edit the information that has already
been entered. The problem I am having is when I enter the data in the 1st
userform if I don't put a character in each textbox in the userform then when
I go to find the information it won't allow me to edit and save any of my
changes made to the project. Currently I am just putting a "x" into each
textbox so I can edit/save it, but with 45+ Textboxes on the userform this is
tedious. Is their anyway to make it so if nothing was entered I can still
edit/save it. Here is a portion of the code that I am currently using (the
rest of the code is same as what I have here). Thanks for your help.

Private Sub SaveProjectButton_Click()
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value =
Replace(Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value,
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value,
tbAEName.Value)
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 2).Value =
Replace(Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 2).Value,
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 2).Value,
tbSiteOwnerName.Value)
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 3).Value =
Replace(Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 3).Value,
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 3).Value,
tbPGLead.Value)
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 4).Value =
Replace(Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 4).Value,
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 4).Value,
cbProjectType)
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 5).Value =
Replace(Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 5).Value,
Sheet1.Columns(1).Find(tbProjectNumber.Value).Offset(0, 5).Value,
cbProjectCategory)
Sheet2.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value =
Replace(Sheet2.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value,
Sheet2.Columns(1).Find(tbProjectNumber.Value).Offset(0, 1).Value,
tbAEName.Value)

 

[Greg Wilson]

My take on your situation is that you find the project number (which is
entered in a textbox on a u/f) in column A of sheet 1. Assuming it is found
(if not, perhaps the problem), then you change the contents of the cells
respectively 1, 2, 3, 4 and 5 columns to the right of this cell. The values
entered into these 5 cells are derived from 5 other textboxes on the u/f. You
do likewise for sheet 2 except you only change the contents of the cell
immediately to the right of the cell where the project number is found.

It is not clear to me why the code you provided won't work even if the other
textboxes that provide the replacement values are empty. I offer this
streamlined code in the hope that we might at least achieve simplification
and clarification. With some clarification, others are more likely to join
in. It at least prevents raising an error if the project number is not found.

***Not tested. Possibly botched. Run it on a copy of your project.

Private Sub SaveProjectButton_Click()
Dim c As Range
Dim v As String
v = tbProjectNumber.Value
With Sheet1.Columns(1)
Set c = .Find(v)
If Not c Is Nothing Then
c(1, 2).Replace c(1, 2).Value, tbAEName.Value
c(1, 3).Replace c(1, 3).Value, tbSiteOwnerName.Value
c(1, 4).Replace c(1, 4).Value, tbPGLead.Value
c(1, 5).Replace c(1, 5).Value, cbProjectType
c(1, 6).Replace c(1, 6).Value, cbProjectCategory
End If
End With
With Sheet2.Columns(1)
Set c = .Find(v)
If Not c Is Nothing Then
c(1, 2).Replace c(1, 2).Value, tbAEName.Value
End If
End With
End Sub

Greg

 

[Greg Wilson]

An odd discovery I just made (I think) about Replace is that it seems only to
work within a wks's used range. Tests just now using a new wb found my code
wouldn't run until blank cells referenced by the macro were within the two
wks's used ranges. News to me.

Remedy was to simply suitably place a character on each of the two db wks at
or beyond the range referenced by the macro's Replace function. This solved
the problem. Apparently doesn't have to be any of the cells referenced by
Replace. Just has to expand the used range. Albeit, this based on extremely
cursory testing. You need to confirm.

I suspect this is the actual cause of your problem, namely: If the textboxes
on the first u/f are blank then they don't enter any data into the db wks's
and thus fail to expand their used ranges. Then when the second u/f attempts
the Replace method on blank cells beyond the used range it fails to work and
perhaps raises an error or causes a complication resulting in your situation.
Not sure if that is logical since I'm missing necessary info but advise you
check it out.

Greg

 

[aintlifegrand79]

Greg thanks for your help. What you describe in your second reply is more
along the lines of what I am having a problem with. Let me see if I can
clarify it a bit. When I entered data into the the 46 textboxs in my 1st
userform (ufNewProjects)
if I do not put a value into one of the textboxs it leaves the corresponding
cell in the database blank, exactly like I want it to. The problem is that
when I open my 2nd userform (ufExistingProjects), looks the same as
ufNewProjects just different buttons and code, I hit the Find Project button
I created and it brings up the project based on the Project number entered
into the corresponding textbox. Now ufExistingProject is populated with the
data from that project but now if I want to make changes to the project by
hitting my save button(the code I put in my 1st post) it will not save
anything if I put a value into a textbox that was previously empty. I don't
know if this explains it any better and if not I am sorry, but thanks for
your help.

 

[Greg Wilson]

Point 1:
This is what I was implying in my second post. The remedy was to expand the
used range, say during setup, of the db worksheets. I'm sure you can find a
reason to put text on the worksheet at or beyond the farthest column that wil
be populated by the macro (I believe column F for Sheet1). Experimentally at
least, insert an "x" or something. This will expand the used range in width.
If I have it right, data entry by the macro of project numbers will expand
the used range height automatically. If that's not correct, do similar for a
row.

The used range is not just a concept. Excel for efficiency's sake limits
worksheet memory in some way only to those cells within the rectanglular
range defined by the farthest column and farthest row that have been changed
by data entry or formatting. My take is that cells outside of the used range
have not been initialize in some sense and do not contain an empty string
("") or any data type (or perhaps vbNullString?).

My interpretation is that the Replace function (both mine and your versions)
looks for text in the referenced cells, even if an empty string (""), and
replaces it with the given value. However, for the reason described, the
function fails when it references beyond the used range.

Point 2:
My code offering was only a simplified replica of yours based on
interpretation. It also contains error handling ("If Not c Is Nothing Then")
in case a job number is not found. By itself, it won't fix the problem unless
failure to find a job number is the actual cause. It still should be helpful.

Greg