does84
01-14-2013, 01:33 AM
Hey there,
I'm a newbie when it comes to VBA so I hope you can help me.
I'm developing a form for a school.
In the combobox you can choose the name of a child. (then I want the form to autofill already inserted data)
When the form autofills in the data I get "error 91 (Object Variable or With Block not set)"
The strange thing is that when you look at the error and you stop the macro/ form, the error doesn't occur anymore and the form works fine.
I work with Excel 2007
Here's the code: (sorry for the Dutch in between)
Private Sub NaamKind_Change()
Dim oRNG As Range
Dim oRNG2 As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("invullen groep 6")
Set oRNG = ws.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
Set ws2 = Worksheets("voorblad")
Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
'Naam invullen van kind
LrTlE5.Value = KolomB(NaamKind.Value)
With Gr6Inv
'naam leerkracht invullen
LeerkrGr6.Value = oRNG2.Offset(0, 38).Value
'terughalen tabblad cito e5
LrTlE5.Value = oRNG.Offset(0, 1).Value
DleTlE5.Value = oRNG.Offset(0, 2).Value
NivTlE5.Value = oRNG.Offset(0, 3).Value
IlTlE5.Value = oRNG.Offset(0, 4).Value
OaTlE5.Value = oRNG.Offset(0, 5).Value
LrBlE5.Value = oRNG.Offset(0, 6).Value
DleBlE5.Value = oRNG.Offset(0, 7).Value
NivBlE5.Value = oRNG.Offset(0, 8).Value
IlBlE5.Value = oRNG.Offset(0, 9).Value
OaBlE5.Value = oRNG.Offset(0, 10).Value
LrRE5.Value = oRNG.Offset(0, 11).Value
DleRE5.Value = oRNG.Offset(0, 12).Value
NivRE5.Value = oRNG.Offset(0, 13).Value
IlRE5.Value = oRNG.Offset(0, 14).Value
OaRE5.Value = oRNG.Offset(0, 15).Value
LrSE5.Value = oRNG.Offset(0, 16).Value
DleSE5.Value = oRNG.Offset(0, 17).Value
NivSE5.Value = oRNG.Offset(0, 18).Value
IlSE5.Value = oRNG.Offset(0, 19).Value
OaSE5.Value = oRNG.Offset(0, 20).Value
'invullen tabblad november
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 21).Value = "x" Then AfINov6 = True Else: AfINov6 = False
If oRNG.Offset(0, 22).Value = "x" Then GevINov6 = True Else: GevINov6 = False
If oRNG.Offset(0, 23).Value = "x" Then OnINov6 = True Else: OnINov6 = False
OaINov6.Value = oRNG.Offset(0, 24).Value
'kijk tabbladen invullen
KOVZIpKNov6.Value = oRNG.Offset(0, 25).Value
GCMLIpKNov6.Value = oRNG.Offset(0, 26).Value
GCMGIpKNov6.Value = oRNG.Offset(0, 27).Value
HRMDAIpKNov6.Value = oRNG.Offset(0, 28).Value
KOMCIpKNov6.Value = oRNG.Offset(0, 29).Value
OaIepKNov6.Value = oRNG.Offset(0, 30).Value
VVEBIpKNov6.Value = oRNG.Offset(0, 31).Value
NEOIpKNov6.Value = oRNG.Offset(0, 32).Value
ZelfVIpKNov6.Value = oRNG.Offset(0, 33).Value
ZelfstIpKNov6.Value = oRNG.Offset(0, 34).Value
TWHIpKNov6.Value = oRNG.Offset(0, 35).Value
TSIpKNov6.Value = oRNG.Offset(0, 36).Value
OaIapKNov6.Value = oRNG.Offset(0, 37).Value
VZBBDGMbKNov6.Value = oRNG.Offset(0, 38).Value
KOMAMbKNov6.Value = oRNG.Offset(0, 39).Value
BAMRMbKNov6.Value = oRNG.Offset(0, 40).Value
OaMbKNov6.Value = oRNG.Offset(0, 41).Value
'invullen tabblad februari
LrTlFeb6.Value = oRNG.Offset(0, 42).Value
DleTlFeb6.Value = oRNG.Offset(0, 43).Value
NivTlFeb6.Value = oRNG.Offset(0, 44).Value
IlTlFeb6.Value = oRNG.Offset(0, 45).Value
OaTlFeb6.Value = oRNG.Offset(0, 46).Value
LrBlFeb6.Value = oRNG.Offset(0, 47).Value
DleBlFeb6.Value = oRNG.Offset(0, 48).Value
NivBlFeb6.Value = oRNG.Offset(0, 49).Value
IlBlFeb6.Value = oRNG.Offset(0, 50).Value
OaBlFeb6.Value = oRNG.Offset(0, 51).Value
LrRFeb6.Value = oRNG.Offset(0, 52).Value
DleRFeb6.Value = oRNG.Offset(0, 53).Value
NivRFeb6.Value = oRNG.Offset(0, 54).Value
IlRFeb6.Value = oRNG.Offset(0, 55).Value
OaRFeb6.Value = oRNG.Offset(0, 56).Value
LrSFeb6.Value = oRNG.Offset(0, 57).Value
DleSFeb6.Value = oRNG.Offset(0, 58).Value
NivSFeb6.Value = oRNG.Offset(0, 59).Value
IlSFeb6.Value = oRNG.Offset(0, 60).Value
OaSFeb6.Value = oRNG.Offset(0, 61).Value
'invullen tabblad maart
If oRNG.Offset(0, 62).Value = "x" Then SmtTlMrt6 = "Zwak"
If oRNG.Offset(0, 63).Value = "x" Then SmtTlMrt6 = "Matig"
If oRNG.Offset(0, 64).Value = "x" Then SmtTlMrt6 = "Voldoende"
If oRNG.Offset(0, 65).Value = "x" Then SmtTlMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 66).Value = "x" Then SmtTlMrt6 = "Goed"
OaTlMrt6.Value = oRNG.Offset(0, 67).Value
If oRNG.Offset(0, 68).Value = "x" Then SmtBlMrt6 = "Zwak"
If oRNG.Offset(0, 69).Value = "x" Then SmtBlMrt6 = "Matig"
If oRNG.Offset(0, 70).Value = "x" Then SmtBlMrt6 = "Voldoende"
If oRNG.Offset(0, 71).Value = "x" Then SmtBlMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 72).Value = "x" Then SmtBlMrt6 = "Goed"
OaBlMrt6.Value = oRNG.Offset(0, 73).Value
If oRNG.Offset(0, 74).Value = "x" Then SmtRMrt6 = "Zwak"
If oRNG.Offset(0, 75).Value = "x" Then SmtRMrt6 = "Matig"
If oRNG.Offset(0, 76).Value = "x" Then SmtRMrt6 = "Voldoende"
If oRNG.Offset(0, 77).Value = "x" Then SmtRMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 78).Value = "x" Then SmtRMrt6 = "Goed"
OaRMrt6.Value = oRNG.Offset(0, 79).Value
If oRNG.Offset(0, 80).Value = "x" Then SmtSMrt6 = "Zwak"
If oRNG.Offset(0, 81).Value = "x" Then SmtSMrt6 = "Matig"
If oRNG.Offset(0, 82).Value = "x" Then SmtSMrt6 = "Voldoende"
If oRNG.Offset(0, 83).Value = "x" Then SmtSMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 84).Value = "x" Then SmtSMrt6 = "Goed"
OaSMrt6.Value = oRNG.Offset(0, 85).Value
'invullen tabblad mei
'invullen tabblad entreetoets gr 5
TlEn5Mei6.Value = oRNG.Offset(0, 86).Value
OaTlEn5Mei6.Value = oRNG.Offset(0, 87).Value
BlEn5Mei6.Value = oRNG.Offset(0, 88).Value
OaBlEn5Mei6.Value = oRNG.Offset(0, 89).Value
REn5Mei6.Value = oRNG.Offset(0, 90).Value
OaREn5Mei6.Value = oRNG.Offset(0, 91).Value
SEn5Mei6.Value = oRNG.Offset(0, 92).Value
OaSEn5Mei6.Value = oRNG.Offset(0, 93).Value
If oRNG.Offset(0, 94).Value = "x" Then TEn5Mei6 = "V"
If oRNG.Offset(0, 95).Value = "x" Then TEn5Mei6 = "IV"
If oRNG.Offset(0, 96).Value = "x" Then TEn5Mei6 = "III"
If oRNG.Offset(0, 97).Value = "x" Then TEn5Mei6 = "II"
If oRNG.Offset(0, 98).Value = "x" Then TEn5Mei6 = "I"
'invullen tabblad entreetoets gr 6
TlEn6Mei6.Value = oRNG.Offset(0, 99).Value
OaTlEn6Mei6.Value = oRNG.Offset(0, 100).Value
BlEn6Mei6.Value = oRNG.Offset(0, 101).Value
OaBlEn6Mei6.Value = oRNG.Offset(0, 102).Value
REn6Mei6.Value = oRNG.Offset(0, 103).Value
OaREn6Mei6.Value = oRNG.Offset(0, 104).Value
SEn6Mei6.Value = oRNG.Offset(0, 105).Value
OaSEn6Mei6.Value = oRNG.Offset(0, 106).Value
If oRNG.Offset(0, 107).Value = "x" Then TEn6Mei6 = "V"
If oRNG.Offset(0, 108).Value = "x" Then TEn6Mei6 = "IV"
If oRNG.Offset(0, 109).Value = "x" Then TEn6Mei6 = "III"
If oRNG.Offset(0, 110).Value = "x" Then TEn6Mei6 = "II"
If oRNG.Offset(0, 111).Value = "x" Then TEn6Mei6 = "I"
'invullen tabblad instructie
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 112).Value = "x" Then AfIMei6 = True Else: AfIMei6 = False
If oRNG.Offset(0, 113).Value = "x" Then GevIMei6 = True Else: GevIMei6 = False
If oRNG.Offset(0, 114).Value = "x" Then OnIMei6 = True Else: OnIMei6 = False
OaIMei6.Value = oRNG.Offset(0, 115).Value
'invullen tabblad kijk
KOVZIpKMei6.Value = oRNG.Offset(0, 116).Value
GCMLIpKMei6.Value = oRNG.Offset(0, 117).Value
GCMGIpKMei6.Value = oRNG.Offset(0, 118).Value
HRMDAIpKMei6.Value = oRNG.Offset(0, 119).Value
KOMCIpKMei6.Value = oRNG.Offset(0, 120).Value
OaIepKMei6.Value = oRNG.Offset(0, 121).Value
VVEBIpKMei6.Value = oRNG.Offset(0, 122).Value
NEOIpKMei6.Value = oRNG.Offset(0, 123).Value
ZelfvIpKMei6.Value = oRNG.Offset(0, 124).Value
ZelfstIpKMei6.Value = oRNG.Offset(0, 125).Value
TWHIpKMei6.Value = oRNG.Offset(0, 126).Value
TSIpKMei6.Value = oRNG.Offset(0, 127).Value
OaIapKMei6.Value = oRNG.Offset(0, 128).Value
VZBBDGMbKMei6.Value = oRNG.Offset(0, 129).Value
KOMAMbKMei6.Value = oRNG.Offset(0, 130).Value
BAMRMbKMei6.Value = oRNG.Offset(0, 131).Value
OaMbKMei6.Value = oRNG.Offset(0, 132).Value
End With
End Sub
It's a whole list, but I hope you can help me.
The error occurs at this line:
LrTlE5.Value = oRNG.Offset(0, 1).Value
Thanks for you trouble (and time)
I'm a newbie when it comes to VBA so I hope you can help me.
I'm developing a form for a school.
In the combobox you can choose the name of a child. (then I want the form to autofill already inserted data)
When the form autofills in the data I get "error 91 (Object Variable or With Block not set)"
The strange thing is that when you look at the error and you stop the macro/ form, the error doesn't occur anymore and the form works fine.
I work with Excel 2007
Here's the code: (sorry for the Dutch in between)
Private Sub NaamKind_Change()
Dim oRNG As Range
Dim oRNG2 As Range
Dim ws As Worksheet
Dim ws2 As Worksheet
Set ws = Worksheets("invullen groep 6")
Set oRNG = ws.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
Set ws2 = Worksheets("voorblad")
Set oRNG2 = ws2.Cells.Find(what:=NaamKind.Value, lookat:=xlWhole)
'Naam invullen van kind
LrTlE5.Value = KolomB(NaamKind.Value)
With Gr6Inv
'naam leerkracht invullen
LeerkrGr6.Value = oRNG2.Offset(0, 38).Value
'terughalen tabblad cito e5
LrTlE5.Value = oRNG.Offset(0, 1).Value
DleTlE5.Value = oRNG.Offset(0, 2).Value
NivTlE5.Value = oRNG.Offset(0, 3).Value
IlTlE5.Value = oRNG.Offset(0, 4).Value
OaTlE5.Value = oRNG.Offset(0, 5).Value
LrBlE5.Value = oRNG.Offset(0, 6).Value
DleBlE5.Value = oRNG.Offset(0, 7).Value
NivBlE5.Value = oRNG.Offset(0, 8).Value
IlBlE5.Value = oRNG.Offset(0, 9).Value
OaBlE5.Value = oRNG.Offset(0, 10).Value
LrRE5.Value = oRNG.Offset(0, 11).Value
DleRE5.Value = oRNG.Offset(0, 12).Value
NivRE5.Value = oRNG.Offset(0, 13).Value
IlRE5.Value = oRNG.Offset(0, 14).Value
OaRE5.Value = oRNG.Offset(0, 15).Value
LrSE5.Value = oRNG.Offset(0, 16).Value
DleSE5.Value = oRNG.Offset(0, 17).Value
NivSE5.Value = oRNG.Offset(0, 18).Value
IlSE5.Value = oRNG.Offset(0, 19).Value
OaSE5.Value = oRNG.Offset(0, 20).Value
'invullen tabblad november
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 21).Value = "x" Then AfINov6 = True Else: AfINov6 = False
If oRNG.Offset(0, 22).Value = "x" Then GevINov6 = True Else: GevINov6 = False
If oRNG.Offset(0, 23).Value = "x" Then OnINov6 = True Else: OnINov6 = False
OaINov6.Value = oRNG.Offset(0, 24).Value
'kijk tabbladen invullen
KOVZIpKNov6.Value = oRNG.Offset(0, 25).Value
GCMLIpKNov6.Value = oRNG.Offset(0, 26).Value
GCMGIpKNov6.Value = oRNG.Offset(0, 27).Value
HRMDAIpKNov6.Value = oRNG.Offset(0, 28).Value
KOMCIpKNov6.Value = oRNG.Offset(0, 29).Value
OaIepKNov6.Value = oRNG.Offset(0, 30).Value
VVEBIpKNov6.Value = oRNG.Offset(0, 31).Value
NEOIpKNov6.Value = oRNG.Offset(0, 32).Value
ZelfVIpKNov6.Value = oRNG.Offset(0, 33).Value
ZelfstIpKNov6.Value = oRNG.Offset(0, 34).Value
TWHIpKNov6.Value = oRNG.Offset(0, 35).Value
TSIpKNov6.Value = oRNG.Offset(0, 36).Value
OaIapKNov6.Value = oRNG.Offset(0, 37).Value
VZBBDGMbKNov6.Value = oRNG.Offset(0, 38).Value
KOMAMbKNov6.Value = oRNG.Offset(0, 39).Value
BAMRMbKNov6.Value = oRNG.Offset(0, 40).Value
OaMbKNov6.Value = oRNG.Offset(0, 41).Value
'invullen tabblad februari
LrTlFeb6.Value = oRNG.Offset(0, 42).Value
DleTlFeb6.Value = oRNG.Offset(0, 43).Value
NivTlFeb6.Value = oRNG.Offset(0, 44).Value
IlTlFeb6.Value = oRNG.Offset(0, 45).Value
OaTlFeb6.Value = oRNG.Offset(0, 46).Value
LrBlFeb6.Value = oRNG.Offset(0, 47).Value
DleBlFeb6.Value = oRNG.Offset(0, 48).Value
NivBlFeb6.Value = oRNG.Offset(0, 49).Value
IlBlFeb6.Value = oRNG.Offset(0, 50).Value
OaBlFeb6.Value = oRNG.Offset(0, 51).Value
LrRFeb6.Value = oRNG.Offset(0, 52).Value
DleRFeb6.Value = oRNG.Offset(0, 53).Value
NivRFeb6.Value = oRNG.Offset(0, 54).Value
IlRFeb6.Value = oRNG.Offset(0, 55).Value
OaRFeb6.Value = oRNG.Offset(0, 56).Value
LrSFeb6.Value = oRNG.Offset(0, 57).Value
DleSFeb6.Value = oRNG.Offset(0, 58).Value
NivSFeb6.Value = oRNG.Offset(0, 59).Value
IlSFeb6.Value = oRNG.Offset(0, 60).Value
OaSFeb6.Value = oRNG.Offset(0, 61).Value
'invullen tabblad maart
If oRNG.Offset(0, 62).Value = "x" Then SmtTlMrt6 = "Zwak"
If oRNG.Offset(0, 63).Value = "x" Then SmtTlMrt6 = "Matig"
If oRNG.Offset(0, 64).Value = "x" Then SmtTlMrt6 = "Voldoende"
If oRNG.Offset(0, 65).Value = "x" Then SmtTlMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 66).Value = "x" Then SmtTlMrt6 = "Goed"
OaTlMrt6.Value = oRNG.Offset(0, 67).Value
If oRNG.Offset(0, 68).Value = "x" Then SmtBlMrt6 = "Zwak"
If oRNG.Offset(0, 69).Value = "x" Then SmtBlMrt6 = "Matig"
If oRNG.Offset(0, 70).Value = "x" Then SmtBlMrt6 = "Voldoende"
If oRNG.Offset(0, 71).Value = "x" Then SmtBlMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 72).Value = "x" Then SmtBlMrt6 = "Goed"
OaBlMrt6.Value = oRNG.Offset(0, 73).Value
If oRNG.Offset(0, 74).Value = "x" Then SmtRMrt6 = "Zwak"
If oRNG.Offset(0, 75).Value = "x" Then SmtRMrt6 = "Matig"
If oRNG.Offset(0, 76).Value = "x" Then SmtRMrt6 = "Voldoende"
If oRNG.Offset(0, 77).Value = "x" Then SmtRMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 78).Value = "x" Then SmtRMrt6 = "Goed"
OaRMrt6.Value = oRNG.Offset(0, 79).Value
If oRNG.Offset(0, 80).Value = "x" Then SmtSMrt6 = "Zwak"
If oRNG.Offset(0, 81).Value = "x" Then SmtSMrt6 = "Matig"
If oRNG.Offset(0, 82).Value = "x" Then SmtSMrt6 = "Voldoende"
If oRNG.Offset(0, 83).Value = "x" Then SmtSMrt6 = "Ruim voldoende"
If oRNG.Offset(0, 84).Value = "x" Then SmtSMrt6 = "Goed"
OaSMrt6.Value = oRNG.Offset(0, 85).Value
'invullen tabblad mei
'invullen tabblad entreetoets gr 5
TlEn5Mei6.Value = oRNG.Offset(0, 86).Value
OaTlEn5Mei6.Value = oRNG.Offset(0, 87).Value
BlEn5Mei6.Value = oRNG.Offset(0, 88).Value
OaBlEn5Mei6.Value = oRNG.Offset(0, 89).Value
REn5Mei6.Value = oRNG.Offset(0, 90).Value
OaREn5Mei6.Value = oRNG.Offset(0, 91).Value
SEn5Mei6.Value = oRNG.Offset(0, 92).Value
OaSEn5Mei6.Value = oRNG.Offset(0, 93).Value
If oRNG.Offset(0, 94).Value = "x" Then TEn5Mei6 = "V"
If oRNG.Offset(0, 95).Value = "x" Then TEn5Mei6 = "IV"
If oRNG.Offset(0, 96).Value = "x" Then TEn5Mei6 = "III"
If oRNG.Offset(0, 97).Value = "x" Then TEn5Mei6 = "II"
If oRNG.Offset(0, 98).Value = "x" Then TEn5Mei6 = "I"
'invullen tabblad entreetoets gr 6
TlEn6Mei6.Value = oRNG.Offset(0, 99).Value
OaTlEn6Mei6.Value = oRNG.Offset(0, 100).Value
BlEn6Mei6.Value = oRNG.Offset(0, 101).Value
OaBlEn6Mei6.Value = oRNG.Offset(0, 102).Value
REn6Mei6.Value = oRNG.Offset(0, 103).Value
OaREn6Mei6.Value = oRNG.Offset(0, 104).Value
SEn6Mei6.Value = oRNG.Offset(0, 105).Value
OaSEn6Mei6.Value = oRNG.Offset(0, 106).Value
If oRNG.Offset(0, 107).Value = "x" Then TEn6Mei6 = "V"
If oRNG.Offset(0, 108).Value = "x" Then TEn6Mei6 = "IV"
If oRNG.Offset(0, 109).Value = "x" Then TEn6Mei6 = "III"
If oRNG.Offset(0, 110).Value = "x" Then TEn6Mei6 = "II"
If oRNG.Offset(0, 111).Value = "x" Then TEn6Mei6 = "I"
'invullen tabblad instructie
'zorgen voor de vinkjes bij instructie
If oRNG.Offset(0, 112).Value = "x" Then AfIMei6 = True Else: AfIMei6 = False
If oRNG.Offset(0, 113).Value = "x" Then GevIMei6 = True Else: GevIMei6 = False
If oRNG.Offset(0, 114).Value = "x" Then OnIMei6 = True Else: OnIMei6 = False
OaIMei6.Value = oRNG.Offset(0, 115).Value
'invullen tabblad kijk
KOVZIpKMei6.Value = oRNG.Offset(0, 116).Value
GCMLIpKMei6.Value = oRNG.Offset(0, 117).Value
GCMGIpKMei6.Value = oRNG.Offset(0, 118).Value
HRMDAIpKMei6.Value = oRNG.Offset(0, 119).Value
KOMCIpKMei6.Value = oRNG.Offset(0, 120).Value
OaIepKMei6.Value = oRNG.Offset(0, 121).Value
VVEBIpKMei6.Value = oRNG.Offset(0, 122).Value
NEOIpKMei6.Value = oRNG.Offset(0, 123).Value
ZelfvIpKMei6.Value = oRNG.Offset(0, 124).Value
ZelfstIpKMei6.Value = oRNG.Offset(0, 125).Value
TWHIpKMei6.Value = oRNG.Offset(0, 126).Value
TSIpKMei6.Value = oRNG.Offset(0, 127).Value
OaIapKMei6.Value = oRNG.Offset(0, 128).Value
VZBBDGMbKMei6.Value = oRNG.Offset(0, 129).Value
KOMAMbKMei6.Value = oRNG.Offset(0, 130).Value
BAMRMbKMei6.Value = oRNG.Offset(0, 131).Value
OaMbKMei6.Value = oRNG.Offset(0, 132).Value
End With
End Sub
It's a whole list, but I hope you can help me.
The error occurs at this line:
LrTlE5.Value = oRNG.Offset(0, 1).Value
Thanks for you trouble (and time)