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" wrote:

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



"Greg Wilson" wrote:

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