Run-Time Error 13 VBA

[Yeoman.jeremy]

Hi there.

Not too sure what i can do to fix this coding, basically when the checkbox is checked, it adds one to a particular cell on another workbook.

Sub CheckBox1_Click()
strFirstFile = "C:\Users\owner\Desktop\Test Over WBK"
strSecondFile = "C:\Users\owner\Desktop\Statistics Runpage"
If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = True Then

Set wbk = Workbooks.Open(strFirstFile)
Set wbk = ThisWorkbook
With wbk.Sheets("Sheet1")
[E4] = [E4] + 1
[F4] = [F4] - 1
End With
End If

Set wbk = strSecondFile
With wbk.Sheets("sheet1")
If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = False Then
Set wbk = strFirstFile
With wbk.Sheets("Sheet1")
[F4] = [F4] + 1
[E4] = [E4] - 1

End With
End If
End With
End Sub

When i run this it says Run-Time Error 13: Type Mismatch

 

[Norie]

What happened to Workbooks.Open(strSecondFile)?

 

[shg]

When you open a workbook, you need a fully qualified path unless it's in the current directory.

When you refer to an open workbook, you never include the path; you are referring to the workbook in memory.

 

[Yeoman.jeremy]

Do i need to use "Open" there too?

i had just assumed that since the workbook was already open i didn't need to?
Hmm maybe i am looking at the code the wrong way

 

[Yeoman.jeremy]

When you open a workbook, you need a fully qualified path unless it's in the current directory.

When you refer to an open workbook, you never include the path; you are referring to the workbook in memory

So how would i code to refer to the open workbook?
would it be something like

strSecondFile = ThisWorkbook

?

 

[Norie]

Well that's my mistake, I thought you were opening the workbook.

shg is right, you refer to open workbooks by their name.

If you want to create a reference to the code the worbook is in however you can use something like this.

Set wbThis =  ThisWorkbook

But I'm not actually sure what you want to do, particularly because you seem to be working with more than one workbook but trying to use the same variable to refer to them.

 

[Yeoman.jeremy]

Let me give you the basic rundown.
I have been developing a quoting program for work, in which i have nearly perfected within excel.
All i really need now, is to link it with a different workbook which will have all the statistics from the quotes on it.

In essence, i have a checkbox which will say "Quote accepted" for which when is ticked will add one to the celll in the other workbook, also taking one off the other cell which is labelled 'not accepted'
Unchecking the checkbox does the opposite.

Does this make much sense to you?

 

[Norie]

That helps but it doesn't quite match with the code.

How many workbooks are there involved when you are using the code?

Which workbooks are open/closed?

Where is the code located?

 

[Yeoman.jeremy]

There are two workbooks involved at any one time.
Right now, i am just trying to figure out the code, so the files arent the actual quoting workbooks.

The code is located in the workbook "Statistics Runpage," as is the checkbox.
The workbook i want to record the statistics on however is called "Test Over WBK", and this one is initially closed.

 

[Norie]

Well you could start the code like this, which creates 2 workbook references.

The first is to the workbook the code is in and used ThisWorkbook.

The second is set when you open the 'Test Over WBk' workbook.

Dim wbStats As Workbook
Dim wbTest As Workbook
Dim strTestFileName As Workbook
Dim srtPath As String
 
   Set wbStats = ThisWorkbook ' create reference to the workbook the code is in
 
   strPath = "C:\ThePath\"  ' path for Test Over WBk, change as needed
 
   strTestFileName = "Test Over WBk.xls"
 
   ' open and create a reference to the 'Test Over WBk' workbook
   Set wbTest = Workbooks.Open(strPath & strTestFileName)
 
   ' code to do stuff

The 2 references can the be used in subsequent whenever you need to refer to them.

 

[Yeoman.jeremy]

so which are the two references i use?
Are they "wbTest" and "wbStats"?
Is the rest of the code the same though? i mean, with the exception of changing the names