PDA

View Full Version : Solved: type mismatch on open



barebum
09-29-2007, 02:20 AM
I am opening a workbook from another and get an error of type mismatch after it has run the code in the second workbook on opening. The code work great if opened manually. The error occurs at end sub.

Here is the code:


Private Sub Workbook_Open()
If Evaluate("=isblank(name)") = False Then GoTo Non

nam = InputBox(Prompt:="Please enter your name", Title:="NEW WEEK")
If nam = "" Then Exit Sub
Range("totals!name").Value = nam
hos = InputBox(Prompt:="Please enter your number", Title:="number")
If hos = "" Then Exit Sub
Range("totals!hosnum").Value = "NUMBER"
Range("totals!hosnum1").Value = hos

' retreive the file name from diet
workfil = Workbooks("diet.xlsm").Worksheets("Sheet1").Range("shfname")

' save the file
Application.DisplayAlerts = False
SaveAs Filename:=workfil
Application.DisplayAlerts = True

Non:


End Sub

daniel_d_n_r
09-29-2007, 02:25 AM
whats this?

If Evaluate("=isblank(name)") = False Then GoTo Non

Bob Phillips
09-29-2007, 02:39 AM
Is it not the SaveAs statement, you have to say what you are saving such as Activeworkbook.

daniel_d_n_r
09-29-2007, 07:26 AM
is this a named range?
are you trying to put a range of values into a variable?

Range("totals!name").Value = nam

barebum
09-29-2007, 06:14 PM
whats this?

If Evaluate("=isblank(name)") = False Then GoTo Non


Evaluate is just that. Evaluates the contents of a cell. isblank is evaluating if the contents is blank in cell. (name) is the name of the cell. i.e. changed from c15 to name.
NON is a label that it jumps to if it is found to be false.

barebum
09-29-2007, 06:15 PM
Not trying to save anything.

barebum
09-29-2007, 06:18 PM
The code works well if workbook is opened manually. No errors occur. It is only when the workbook is opened from another workbook that the error occurs.

johnske
09-29-2007, 06:40 PM
The code works well if workbook is opened manually. No errors occur. It is only when the workbook is opened from another workbook that the error occurs.by default,

Range("totals!hosnum").Value = "NUMBER"
refers to

ActiveWorkbook.ActiveSheet.Range("totals!hosnum").Value = "NUMBER"
when the workbook is opened from another workbook you need to use explicit references to the workbook and worksheet to which the code is to apply...

Bob Phillips
09-29-2007, 11:53 PM
Not trying to save anything.

Then what is



SaveAs Filename:=workfil

barebum
09-30-2007, 06:01 PM
Then what is



SaveAs Filename:=workfil


This is not where the error is occuring. The code works provided the workbook is not opened from another workbook. If it is the error occurs at "End sub". The rest of the code completed successfully. Including the saveas.

johnske
09-30-2007, 06:21 PM
post the workbook.

Bob Phillips
10-01-2007, 01:40 AM
This is not where the error is occuring. The code works provided the workbook is not opened from another workbook. If it is the error occurs at "End sub". The rest of the code completed successfully. Including the saveas.

Maybe that is so, but you said you wern't doing a save. That line suggests that you are at least trying to.

barebum
10-01-2007, 08:09 PM
Maybe that is so, but you said you wern't doing a save. That line suggests that you are at least trying to.

Based on this then it we could assume that the save is causing the problem. I tryed error trapping after the save and it still gave the same error at the End Sub. This code also runs perfectly as long as it is not trying to run after the workbook is open from another workbook.

barebum
10-01-2007, 10:14 PM
The problem was that it was trying to run the open workbook line in the previous workbook. The solution was to put a on error resume next command before and after the open workbook line. I tried before only and after only and that didn't work. It only worked when it was before and after.

TonyJollans
10-01-2007, 11:56 PM
I'm just a Wordie passing through so feel free to ignore me :)

I am no wiser about anything from having read this whole thread - I don't know what the problem is or was - and the 'solution', it seems, is to ignore errors.

Bob Phillips
10-02-2007, 01:05 AM
You are not alone TJ. The 'problem' has never been properly articulated, requests for a workbook, or information have been ignored. Pointing out non-working code has been dismissed by the OP as not relevant without any explanation as to why.

I for one will reflect on any further posts by this OP.