Object Variable Error

J

Janet BN

Hi, I'm hoping someone can help. I am not a VBA programmer by any stretch of
the imagination, but I have managed to create a form and the supporting code,
to update a new project template. However, I keep getting:

Unexpected Error Occurred
91: Object variable or With block variable not set

Even though all the fields get updated and I have checked all the objects a
number of times to see if one is missing some definition (and I dont have a
with statement in the code). Is there anything else that causes this error,
but does not stop the update?

All replies greatly appreciated.
 
R

Rod Gill

Can you email the offending code and previous statements relating to the
statement causing the error please? On its own there are many possible
causes of the error so we need more info to go on. Also what version of
Project and what SP number?
 
J

Janet BN

Thanks Rod,

I am running MSP 2003 SP2. As I said the code is pretty rough but it does
work for the most part (no laughing too loud, LOL). We are a building
company and the form loads the client personal data to a template, before I
manually include the file in the Master Schedule.

As for the statement causing the problem - don't know, the code appears to
finish before it shows the error (doesn't ask to go to debug mode) so I am
unable to isolate the problem. Hope this helps and thanks again.

Code is:

Private Sub UserForm_Initialize()

Application.ActiveProject.Activate
TableApply name:="New Clients"
SelectSheet

Load frmAddClient
frmAddClient.show

'Load form info
'cboRepayment.List() = Array("Private", "Public", "Titan", "Other
Developer", _
"Other")
End Sub

Private Sub cmdOK_Click()

Dim job As String
Dim plan As String
Dim clname As String
Dim contact As String
Dim build As String
Dim claddress As String
Dim mobile As String
Dim phone As String
Dim email As String
Dim oc As String
Dim com As String
Dim referred As String
Dim depd As String
Dim depa As String
Dim uncondd As String
Dim unconda As String
Dim lot As String


'Set values from Selections
job = txbJobNo.Value
plan = txbPlan.Value
clname = txbClientName.Value
contact = txbContactName.Value
build = txbBuildLoc.Value
claddress = txbClientMail.Value
mobile = txbMobile.Value
phone = txbPhone.Value
email = txbEmail.Value
oc = txbOwnersCare.Value
com = txbComments.Value
referred = txbReferred.Value
depd = txbDepositDate.Value
depa = txbDepositAmt.Value
uncondd = txbUncondDate.Value
unconda = txbUncondAmt.Value
lot = txbLot.Value


SelectTaskField Row:=1, Column:="Job No"

Application.Replace Field:="Job No", Replacement:=job, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Plan", Replacement:=plan, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Client", Replacement:=clname,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Contact", Replacement:=contact,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Site Address", Replacement:=build,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Address", Replacement:=claddress,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Mobile Phone No", Replacement:=mobile,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Hm-Wk Phone No", Replacement:=phone,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Client Email", Replacement:=email,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Notes", Replacement:=oc, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Referred By", Replacement:=referred,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Lot", Replacement:=lot, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="Notes"
Application.Replace Field:="Notes", Replacement:=com, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="All Tasks"

FilterApply name:="Deposits"
Application.Replace Field:="Actual Start", Replacement:=depd,
test:="equals", Value:="01/01/01", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Cost", Replacement:=depa, test:="equals",
Value:="$0.00", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="All Tasks"

FilterApply name:="Uncond"
Application.Replace Field:="Actual Start", Replacement:=uncondd,
test:="equals", Value:="01/01/01", _
ReplaceAll:=True, Next:=False, MatchCase:=False

Application.Replace Field:="Cost", Replacement:=unconda, test:="equals",
Value:="$0.00", _
ReplaceAll:=True, Next:=False, MatchCase:=False

Unload frmAddClient

Application.ActiveProject.Activate
TableApply name:="Entry"
FilterApply name:="All Tasks"

End Sub
 
J

Janet BN

Sorry Rod, that is MSP Standard 2003 SP2.

Janet BN said:
Thanks Rod,

I am running MSP 2003 SP2. As I said the code is pretty rough but it does
work for the most part (no laughing too loud, LOL). We are a building
company and the form loads the client personal data to a template, before I
manually include the file in the Master Schedule.

As for the statement causing the problem - don't know, the code appears to
finish before it shows the error (doesn't ask to go to debug mode) so I am
unable to isolate the problem. Hope this helps and thanks again.

Code is:

Private Sub UserForm_Initialize()

Application.ActiveProject.Activate
TableApply name:="New Clients"
SelectSheet

Load frmAddClient
frmAddClient.show

'Load form info
'cboRepayment.List() = Array("Private", "Public", "Titan", "Other
Developer", _
"Other")
End Sub

Private Sub cmdOK_Click()

Dim job As String
Dim plan As String
Dim clname As String
Dim contact As String
Dim build As String
Dim claddress As String
Dim mobile As String
Dim phone As String
Dim email As String
Dim oc As String
Dim com As String
Dim referred As String
Dim depd As String
Dim depa As String
Dim uncondd As String
Dim unconda As String
Dim lot As String


'Set values from Selections
job = txbJobNo.Value
plan = txbPlan.Value
clname = txbClientName.Value
contact = txbContactName.Value
build = txbBuildLoc.Value
claddress = txbClientMail.Value
mobile = txbMobile.Value
phone = txbPhone.Value
email = txbEmail.Value
oc = txbOwnersCare.Value
com = txbComments.Value
referred = txbReferred.Value
depd = txbDepositDate.Value
depa = txbDepositAmt.Value
uncondd = txbUncondDate.Value
unconda = txbUncondAmt.Value
lot = txbLot.Value


SelectTaskField Row:=1, Column:="Job No"

Application.Replace Field:="Job No", Replacement:=job, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Plan", Replacement:=plan, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Client", Replacement:=clname,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Contact", Replacement:=contact,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Site Address", Replacement:=build,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Address", Replacement:=claddress,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Mobile Phone No", Replacement:=mobile,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Hm-Wk Phone No", Replacement:=phone,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Client Email", Replacement:=email,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Notes", Replacement:=oc, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Referred By", Replacement:=referred,
test:="contains", Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Lot", Replacement:=lot, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="Notes"
Application.Replace Field:="Notes", Replacement:=com, test:="contains",
Value:="", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="All Tasks"

FilterApply name:="Deposits"
Application.Replace Field:="Actual Start", Replacement:=depd,
test:="equals", Value:="01/01/01", _
ReplaceAll:=True, Next:=False, MatchCase:=False
Application.Replace Field:="Cost", Replacement:=depa, test:="equals",
Value:="$0.00", _
ReplaceAll:=True, Next:=False, MatchCase:=False

FilterApply name:="All Tasks"

FilterApply name:="Uncond"
Application.Replace Field:="Actual Start", Replacement:=uncondd,
test:="equals", Value:="01/01/01", _
ReplaceAll:=True, Next:=False, MatchCase:=False

Application.Replace Field:="Cost", Replacement:=unconda, test:="equals",
Value:="$0.00", _
ReplaceAll:=True, Next:=False, MatchCase:=False

Unload frmAddClient

Application.ActiveProject.Activate
TableApply name:="Entry"
FilterApply name:="All Tasks"

End Sub
Click to expand...
 
R

Rod Gill

It's very strange that you don't get a debug option. What you need to do is
add some debug statements:

Debug.Print "1"
Debug.Print "2"

etc. Add 1 halfway down the code and run the macro again. after running look
at the immediate window (from code window press Ctrl+G) and see if the 1
appeared. If it didn't, add debug statements in first half of code, else
second half of code. This way you will eventually find which statement is
causing the problem.
 
J

Janet BN

Thanks Rod, I will give that a go.

Rod Gill said:
It's very strange that you don't get a debug option. What you need to do is
add some debug statements:

Debug.Print "1"
Debug.Print "2"

etc. Add 1 halfway down the code and run the macro again. after running look
at the immediate window (from code window press Ctrl+G) and see if the 1
appeared. If it didn't, add debug statements in first half of code, else
second half of code. This way you will eventually find which statement is
causing the problem.

--

Rod Gill
Project MVP
Visit www.msproject-systems.com for Project Companion Tools and more
Click to expand...
 
J

Janet BN

Hi Rod, I think I have isolated the problem at the "Unload form" area. I
used the debug statements (and even stepped it thru again) and everything
else seems to work ok but it always stops at the end.

I commented out all the lines after this statement
'Application.ActiveProject. Activate, 'TableApply name:="Entry") and it still
stopped.

So, do I need to set a Dim statement and identify the form name? Does the
form need a specific setting that I may not have set?

Thanks once again...
 
R

Rod Gill

Application.ActiveProject. Activate does nothing. It just makes the active
project active again.
'TableApply name:="Entry" is fine, provided the table Entry still exists.

Try:

frmAddClient.Hide instead of the Unload command
 
J

Janet BN

Hi again Rod, I tried to hide the form, but it wont disappear. I have
managed to find someone locally who can look at it and maybe figure it out.
I have other forms where the code is similar, but they don't cause me any
problems - so it will pay to have another set of eyes I think.

Thank-you for all your help.
 

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

VSTO C# add in Outlook.Appointment error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Embed the same Excel multiple times 0
Using While Wend with Cells.Find 1
VBA Assigning cell value to variable giving runtime error 3
Object Browser in VBA 0
Timescalevalue update gives error 1101 0
If Cells.Find returns run-time error '91': Object variableor With block variable not set 1

Top