ianohlander
New Member
- Joined
- Dec 5, 2002
- Messages
- 26
Hello all. Back again this a seemingly undecipherable problem.
I have the following code:
The thing is, this part of the sub works perfectly if the VB Editor is open. When it is executed, the sheet it creates gets that code inside it's module. But if the VB Editor is closed (either on start up or if I close it manually) then this code doesn't work. It gives me a "Object not set or with block not set" error (or something like that). So the "code" string is not insterted into the worksheets module.
I have made sure that this project references MS VB Extensibility 5.3 (to access the VBProject and VBComponent objects.)
What's the deal? Why would a sub work only if the VB Editor where open? What is the VB Editor doing that makes this ok? And how can I make this run without requiring the user open the VBE (which seems incredibly stupid to require)?
This is really aggrivating and I am finding nothing by google-searching it.
IT WORKS, if the VB Editor is open. Just open, mind you. Not run from the Editor. The sub is called from a command button on a worksheet. IF the VB editor is open (but minimized), the sub works. IF the editor is not open, it doesn't work. That simple.
Grrrr!!
Thanks,
Ian
I have the following code:
Code:
. . .
Set nwSheet = Sheets.Add
nwSheet.name = name
. . .
Set UFvbc = ActiveWorkbook.VBProject.VBComponents.Item(nwSheet.CodeName)
Dim count As Integer
count = UFvbc.CodeModule.CountOfLines + 1
code = "" & "Private Sub Worksheet_Change(ByVal Target as Range)" & vbCr
code = code & "myRow = CStr(Target.Row)" & vbCr
code = code & "If Target.Column = 3 And Target.Row > 5 And Target.Row < 59 Then " & vbCr
code = code & "If Target.Value <> """" Then " & vbCr
code = code & "Range(""E"" & myRow).Value= payrollModule.getFed(Range(""L1"").Value, Range(""N1"").Value, Range(""C"" & myRow).Value) " & vbCr
code = code & "Range(""G"" & myRow).Formula = ""=$g$5*e"" & myRow" & vbCr
code = code & "Range(""I"" & myRow).Formula = ""=$i$5*e"" & myRow" & vbCr
code = code & "Range(""K"" & myRow).Formula = ""=$k$5*e"" & myRow" & vbCr
code = code & "Range(""M"" & myRow).Formula = ""=c"" & myRow & "" - e"" & myRow &"" - g"" & myRow & ""- i"" & myRow & ""- k"" & myRow" & vbCr
code = code & "Else" & vbCr
code = code & "Range(""E"" & myRow).ClearContents" & vbCr
code = code & "Range(""G"" & myRow).ClearContents" & vbCr
code = code & "Range(""I"" & myRow).ClearContents" & vbCr
code = code & "Range(""K"" & myRow).ClearContents" & vbCr
code = code & "Range(""M"" & myRow).ClearContents" & vbCr
code = code & "End if" & vbCr
code = code & "End if" & vbCr
code = code & "end sub"
UFvbc.CodeModule.insertLines count, codeThe thing is, this part of the sub works perfectly if the VB Editor is open. When it is executed, the sheet it creates gets that code inside it's module. But if the VB Editor is closed (either on start up or if I close it manually) then this code doesn't work. It gives me a "Object not set or with block not set" error (or something like that). So the "code" string is not insterted into the worksheets module.
I have made sure that this project references MS VB Extensibility 5.3 (to access the VBProject and VBComponent objects.)
What's the deal? Why would a sub work only if the VB Editor where open? What is the VB Editor doing that makes this ok? And how can I make this run without requiring the user open the VBE (which seems incredibly stupid to require)?
This is really aggrivating and I am finding nothing by google-searching it.
IT WORKS, if the VB Editor is open. Just open, mind you. Not run from the Editor. The sub is called from a command button on a worksheet. IF the VB editor is open (but minimized), the sub works. IF the editor is not open, it doesn't work. That simple.
Grrrr!!
Thanks,
Ian
