Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
I'm working with EXCEL 2003 and I'm trying to use the following code:
Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
You would need a reference in the workbook to the Microsoft visual basic
extensibility library where the type CodeModule is defined. Otherwise, you should declare that as the generic Object. -- Regards, Tom Ogilvy "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
You need to reference your project to the Extensibility library.
Tools - Reference - Microsoft Visual Basic for Applications Extensibility ?.? -- HTH... Jim Thomlinson "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
Reference for me is greyed out so that is a porblem. Is there something else
that needs to be enabled in order for that option to be active? Also, if I make that reference in the library does that reference stay with the workbook so it will work for anyone who uses it? I don't want everyone who needs to use this have to make this reference. Tom mentioned declaring a generic object. How would this be done? "Jim Thomlinson" wrote: You need to reference your project to the Extensibility library. Tools - Reference - Microsoft Visual Basic for Applications Extensibility ?.? -- HTH... Jim Thomlinson "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
Dim VBCodeMod As Object
Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With A reference stays with the workbook, but that doesn't mean it will work in another version of excel or even another implementation. In this case, you don't need it if you change your declaration. -- Regards, Tom Ogilvy "hshayh0rn" wrote: Reference for me is greyed out so that is a porblem. Is there something else that needs to be enabled in order for that option to be active? Also, if I make that reference in the library does that reference stay with the workbook so it will work for anyone who uses it? I don't want everyone who needs to use this have to make this reference. Tom mentioned declaring a generic object. How would this be done? "Jim Thomlinson" wrote: You need to reference your project to the Extensibility library. Tools - Reference - Microsoft Visual Basic for Applications Extensibility ?.? -- HTH... Jim Thomlinson "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
I received the following error message from users trying to use the
speadsheet I built: "Programatic access to Visual Basic Project is not trusted" I'm pretty sure the reason for the error is that some of the users running the program do not have the trust access to visual basic project checked. I have read here that there is no way to enable or check that option programtically but I need some way around this error. Maybe someone could help me change my code so I don't need that box checked. Here is the code I'm using right now: Sub DeleteAllVBA() Dim VBComp As Object Dim VBComps As Object Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub "Tom Ogilvy" wrote: Dim VBCodeMod As Object Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With A reference stays with the workbook, but that doesn't mean it will work in another version of excel or even another implementation. In this case, you don't need it if you change your declaration. -- Regards, Tom Ogilvy "hshayh0rn" wrote: Reference for me is greyed out so that is a porblem. Is there something else that needs to be enabled in order for that option to be active? Also, if I make that reference in the library does that reference stay with the workbook so it will work for anyone who uses it? I don't want everyone who needs to use this have to make this reference. Tom mentioned declaring a generic object. How would this be done? "Jim Thomlinson" wrote: You need to reference your project to the Extensibility library. Tools - Reference - Microsoft Visual Basic for Applications Extensibility ?.? -- HTH... Jim Thomlinson "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting VBA code
This is a security setting that is under the control of the user.
Security settings wouldn't be too secure if any developer could change them. Maybe you should ask the users to toggle this setting. hshayh0rn wrote: I received the following error message from users trying to use the speadsheet I built: "Programatic access to Visual Basic Project is not trusted" I'm pretty sure the reason for the error is that some of the users running the program do not have the trust access to visual basic project checked. I have read here that there is no way to enable or check that option programtically but I need some way around this error. Maybe someone could help me change my code so I don't need that box checked. Here is the code I'm using right now: Sub DeleteAllVBA() Dim VBComp As Object Dim VBComps As Object Set VBComps = ActiveWorkbook.VBProject.VBComponents For Each VBComp In VBComps Select Case VBComp.Type Case vbext_ct_StdModule, vbext_ct_MSForm, _ vbext_ct_ClassModule VBComps.Remove VBComp Case Else With VBComp.CodeModule .DeleteLines 1, .CountOfLines End With End Select Next VBComp End Sub "Tom Ogilvy" wrote: Dim VBCodeMod As Object Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With A reference stays with the workbook, but that doesn't mean it will work in another version of excel or even another implementation. In this case, you don't need it if you change your declaration. -- Regards, Tom Ogilvy "hshayh0rn" wrote: Reference for me is greyed out so that is a porblem. Is there something else that needs to be enabled in order for that option to be active? Also, if I make that reference in the library does that reference stay with the workbook so it will work for anyone who uses it? I don't want everyone who needs to use this have to make this reference. Tom mentioned declaring a generic object. How would this be done? "Jim Thomlinson" wrote: You need to reference your project to the Extensibility library. Tools - Reference - Microsoft Visual Basic for Applications Extensibility ?.? -- HTH... Jim Thomlinson "hshayh0rn" wrote: I'm working with EXCEL 2003 and I'm trying to use the following code: Dim VBCodeMod As CodeModule Dim StartLine As Long Dim HowManyLines As Long Set VBCodeMod = ThisWorkbook.VBProject.VBComponents("Startup").Cod eModule With VBCodeMod StartLine = 1 HowManyLines = .CountOfLines .DeleteLines StartLine, HowManyLines End With When it runs I get the following error: Compile Error - User -defined type not defined I have checked to be sure that under tools-macros-security and trusted publishers that both the check boxes are checked which I've seen in other posts could be an issue. So, does anyone know what the issue might be related to the error? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting row using vb code? | Excel Discussion (Misc queries) | |||
VBA Code for Deleting a Row | Excel Worksheet Functions | |||
Deleting Code from VBA | Excel Discussion (Misc queries) | |||
Another code for deleting row | Excel Programming | |||
Deleting code in a file with code.. | Excel Programming |