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

 

[Derek Brown]

You did not say on which line the error occurred. You need to enter Debug mode to find this out.
However, some observations:
1. strFirstFile and strSecondFile do not reference workbooks (no .xls or .xlsx etc.).
2. "Set wbk = strSecondFile" is not correct
3. You need to declare your variables:
Dim strFirstFile as String
Dim wbk as Workbook
etc.
and reset objects at the end of the routine, e.g.:
Set wkb = Nothing
4. "Set wbk = ThisWorkbook" is probably not necessary?

 

[doofusboy]

It's possible you may just need to capitalize this

With wbk.Sheets("sheet1")

as

With wbk.Sheets("[COLOR=red][B]S[/B][/COLOR]heet1")

Usually things inside double quote marks are case-sensitive.

 

[Yeoman.jeremy]

Derek:
I changed most of the things you mentioned to the following

Sub CheckBox1_Click()
Dim wbk As Workbook
Dim strFirstFile As String
Dim strSecondFile As String

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

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

But i wasn't sure what you meant by

' "Set wbk = strSecondFile" is not correct'

how do i fix this up?


Doofus: thanks for the idea, i tried that and no change - handy to know though

 

[Norie]

Try using Range instead of [] and add a few dots.

.Range("E4").Value = .Range("E4").Value+1
.Range("F4").Value = .Range("F4").Value-1

By the way, the case doesn't matter for sheet names but it's probably a good idea to get it right anyway.

 

[Yeoman.jeremy]

.Range("E4").Value = .Range("E4").Value+1
.Range("F4").Value = .Range("F4").Value-1

Great, i changed this and it shows another error D:

Now it says
"Compile Erroor:
End if without Block if"

for the code

If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = True Then
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Sheet1")
Range("E4").Value = Range("E4").Value + 1
Range("F4").Value = Range("F4").Value - 1
Set wbk = Nothing
End If

I've had this problem a few times, and it's very confusing. I don't understand why it's telling me that i don't have an 'IF' when i do

 

[Norie]

What happened to the End With that was in your original code and the dots in the code I posted?

If it still doesn't work when you add those then it's could be a problem with the data.

 

[Yeoman.jeremy]

Oops, didnt even see the end with had gone missing.
Also, i misread the place you positioned the dots, and put it between range and the cell, which VBA spit the dummy at so i removed it.

Here is my whole code

Sub CheckBox1_Click()
Dim wbk As Workbook
Dim strFirstFile As String
Dim strSecondFile As String

strFirstFile = "C:\Users\owner\Desktop\Test Over WBK.xls"
strSecondFile = "C:\Users\owner\Desktop\Statistics Runpage.xls"

If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = True Then
Set wbk = Workbooks.Open(strFirstFile)
With wbk.Sheets("Sheet1")
.Range("E4").Value = Range("E4").Value + 1
.Range("F4").Value = Range("F4").Value - 1
Set wbk = Nothing
End With
End If


Set wbk = Workbooks(strSecondFile)
With wbk.Sheets("Sheet1")
If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = False Then
Set wbk = Nothing
Set wbk = Workbooks(strFirstFile)
With wbk.Sheets("Sheet1")
.Range("F4").Value = Range("F4").Value + 1
.Range("E4").Value = Range("E4").Value - 1

End With
End If
End With
End Sub

Now it says Runtime Error 1004
Application-defined or object-defined error

 

[Norie]

Where are you getting the error now?

I think it might be an idea to remove the first With wbk.Sheets("Sheet1") in the 2nd section of code.

PS As well as telling us the error it's useful to know where/when it's happening.

 

[Yeoman.jeremy]

Haha oh drama drama...
this time the error is within the second part of coding, with

[COLOR="Red"]Set wbk = Workbooks(strSecondFile)[/COLOR]
If ThisWorkbook.Sheets("Sheet1").CheckBox1.Value = False Then
Set wbk = Nothing
Set wbk = Workbooks(strFirstFile)
With wbk.Sheets("Sheet1")
.Range("F4").Value = Range("F4").Value + 1
.Range("E4").Value = Range("E4").Value - 1

"Runtime Error '9':
Subscript our of range"