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

 

[Yeoman.jeremy]

Just Wrote in your Codeing now , feels a lot tidier :P
Only, came across this problem
"Run-time Error '91':
Object Variable or With Block variable not set"

Here's the code, and the spot i found the problem in

Sub CheckBox1_Click()
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:\Users\owner\Desktop\Test Over WBK"
   
   [COLOR="Red"]strTestFileName = "Test Over WBk.xls"[/COLOR]
 
   ' open and create a reference to the 'Test Over WBk' workbook
   Set wbTest = Workbooks.Open(strPath & strTestFileName)
 
   ' code to do stuff
   If wbStats.Sheets("Sheet1").Checkbox1.Value = True Then
   
With wbTest.Sheets("Sheet1")
.Range("E4").Value = Range("E4").Value + 1
.Range("F4").Value = Range("F4").Value - 1
End With
End If

If wbStats.Sheets("Sheet1").Checkbox1.Value = False Then
With wbTest.Sheets("Sheet1")
.Range("E4").Value = Range("E4") - 1
.Range("F4").Value = Range("F4") + 1
End With
End If

 

[Norie]

That's a typo - it should be declared as a string.

That's why it's got str at the beginning and I should have remebered.

The only other problem with the code that I can see is that you appear to have mislaid a few dots again.

 

[Yeoman.jeremy]

blimmin Dots eh

So now it's fining a problem with my IF

[COLOR="Red"]If wbStats.Sheets("Sheet1").Checkbox1.Value = True Then[/COLOR]
   
With wbTest.Sheets("Sheet1")
.Range("E4").Value = Range("E4").Value + 1
.Range("F4").Value = Range("F4").Value - 1
End With
End If

 

[Norie]

And the problem is?

 

[Yeoman.jeremy]

aw hang on, that was what came up when i was stepping through it.
I just ran the macro, and it said it couldn't find the file "C:\Users\owner\Desktop\Test Over WBKTest Over WBK.xls"
My guess is because the coding says

Dim strTestFileName As String
Dim srtPath As String
'
'
'
Set wbTest = Workbooks.Open(strPath & strTestFileName)

 

[Norie]

Well, and this is a wild guess - call me crazy, that's probably because you've included what appears to be the file name in the path.

strPath = "C:\Users\owner\Desktop\Test Over WBK"

 

[Yeoman.jeremy]

Hang on, i figured it out.
The problem was with the

 strPath = "C:\Users\owner\Desktop\Test Over WBK"
   
   strTestFileName = "Test Over WBk.xls"

It was placing them two together, so it was looking for
"C:\Users\owner\Desktop\Test Over WBKTest Over WBK.xls"

and also, for some reason it doesn't like it when i put .xls on the end.

So now this is all running smoothly, except for one problem which i've encountered D:

When i run it, instead of taking the values on the new page and increasing them by one etc, it takes the values from the first page, and increases THOSE by one on the new workbook

 

[Norie]

That sounds like you still don't have the referencing right.

Might even have mislaid a dot or two.

 

[Yeoman.jeremy]

I found the Dots! and it works! YAY!

heh thanks an awful lot!

now to apply it to the real deal and hope i don't mess it all up horribly