Solved: type mismatch on open

[barebum]

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:

[VBA]
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
[/VBA]

[daniel_d_n_r]

whats this?

[VBA]If Evaluate("=isblank(name)") = False Then GoTo Non
[/VBA]

[Bob Phillips]

Is it not the SaveAs statement, you have to say what you are saving such as Activeworkbook.

[daniel_d_n_r]

is this a named range?
are you trying to put a range of values into a variable?

[VBA]Range("totals!name").Value = nam
[/VBA]

[barebum]

whats this?

[vba]If Evaluate("=isblank(name)") = False Then GoTo Non
[/vba]

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]

Not trying to save anything.

[barebum]

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]

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,
[VBA]
Range("totals!hosnum").Value = "NUMBER"
[/VBA]refers to
[VBA]
ActiveWorkbook.ActiveSheet.Range("totals!hosnum").Value = "NUMBER"
[/VBA]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]

Not trying to save anything.

Then what is

[vba]

SaveAs Filename:=workfil
[/vba]

[barebum]

Then what is

[vba]

SaveAs Filename:=workfil
[/vba]

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]

post the workbook.

[Bob Phillips]

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]

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]

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]

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]

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.