Inserting a procedure using VBA code in Excel
Dear Experts
I have a procedure, that inserts a new line in a locked sheet using VBA. This line has to be in a specific place, as it has to be included in the totals for that area. Now We want to create this spreadsheet as a template, and want to have the possibility to insert a new procedure using VBA. In the bottom of this question, you can see the code I have tried to use, which I found on exceltip.com. Everybody on that site writes that it works fine, but not for me.
The original procedure is handled by having the code in a General procedure module and then calling this code from an insert module using the call procedure, and then referring to the sheet name and 2 named ranges (See code below).
I do not know if it is the .codemodule, that creates the problem for me, as I am told that I need VB 5.0 or 6.0 to activate that. But when I activated Microsoft Visual Basic for Applications Extensibility the . codemodule was available.
Can anybody help me with a solution on inserting a new insert line procedure using names that I name in a userform?
The above code is not necessarily needed, if you have an alternative.
regards
Jørgen
I have a procedure, that inserts a new line in a locked sheet using VBA. This line has to be in a specific place, as it has to be included in the totals for that area. Now We want to create this spreadsheet as a template, and want to have the possibility to insert a new procedure using VBA. In the bottom of this question, you can see the code I have tried to use, which I found on exceltip.com. Everybody on that site writes that it works fine, but not for me.
The original procedure is handled by having the code in a General procedure module and then calling this code from an insert module using the call procedure, and then referring to the sheet name and 2 named ranges (See code below).
Sub Insert_Line(SheetName, HiddenLineName, TopOfRegionName)
'---------------------------------------------------------------------------------------
' Procedure : Insert_Line
' Author : Jørgen W. Rasmussen
' Date : 09-12-2011
' Purpose : Dette er den generelle kode, der indsætter en ny linie i regnearket baseret på kriterierne
' Sheetname =
'---------------------------------------------------------------------------------------
With Application
.ScreenUpdating = False
.EnableCancelKey = xlDisabled
End With
Sheet1.Select
Range(HiddenLineName).Select
With Selection
.EntireRow.Hidden = False
.Copy
.Insert Shift:=xlDown
.EntireRow.Hidden = True
End With
With Application
.EnableCancelKey = xlInterrupt
.ScreenUpdating = True
.GoTo (TopOfRegionName)
End With
End Sub 'Insert LineActiveSheet.Unprotect "password"
Call Insert_Line("Sheet1", "Account_line", "Top_of_Account")
ActiveSheet.Protect "password"I have
Public Sub InsertProcedureCode(ByVal wb As Workbook, ByVal InsertToModuleName As String)
' inserts new code in module named InsertToModuleName in wb
' needs customizing depending on the code to insert
Dim VBCM As CodeModule
Dim InsertLineIndex As Long
On Error Resume Next
Set VBCM = wb.VBProject.VBComponents(InsertToModuleName).CodeModule
If Not VBCM Is Nothing Then
With VBCM
InsertLineIndex = .CountOfLines + 1
' customize the next lines depending on the code you want to insert
.InsertLines InsertLineIndex, "Sub NewSubName()" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, _
" Msgbox ""Hello World!"",vbInformation,""Message Box Title""" & Chr(13)
InsertLineIndex = InsertLineIndex + 1
.InsertLines InsertLineIndex, "End Sub" & Chr(13)
' no need for more customizing
End With
Set VBCM = Nothing
End If
On Error GoTo 0
End SubI do not know if it is the .codemodule, that creates the problem for me, as I am told that I need VB 5.0 or 6.0 to activate that. But when I activated Microsoft Visual Basic for Applications Extensibility the . codemodule was available.
Can anybody help me with a solution on inserting a new insert line procedure using names that I name in a userform?
The above code is not necessarily needed, if you have an alternative.
regards
Jørgen
Why do you need to add code programaticly if you are using a template?
Just put the code in the template and it every workbook created from the template will have it.
Just put the code in the template and it every workbook created from the template will have it.
ASKER
Hi both
Actually it is a template to templates for finance people, and I want the template designers to have the possibility to create different types of insert lines, where they in the design proces
can call the topcell and the insert line different things.
It is a possibility to put a number of insertcodes in my template, but that will not generate codetext which describes exactly what is happening in the code.
To MartinLiss
The Original Insert Module works fine with the Sheet1, so that is not a problem.
It is in the new procedure, that I experience problems
regards
Jørgen
Actually it is a template to templates for finance people, and I want the template designers to have the possibility to create different types of insert lines, where they in the design proces
can call the topcell and the insert line different things.
It is a possibility to put a number of insertcodes in my template, but that will not generate codetext which describes exactly what is happening in the code.
To MartinLiss
The Original Insert Module works fine with the Sheet1, so that is not a problem.
It is in the new procedure, that I experience problems
regards
Jørgen
Jørgen
You've lost me a bit.
What do you mean by 'template to templates'?
Why do the template creators need to use code to write code?
Also, if you want to add something that describes the code use comments, whether you are manually creating it or using code to create it.
PS Have you considered the security implications of this? Anybody using code that writes code will have to change their security settings to trust access to the VBA project.
You've lost me a bit.
What do you mean by 'template to templates'?
Why do the template creators need to use code to write code?
Also, if you want to add something that describes the code use comments, whether you are manually creating it or using code to create it.
PS Have you considered the security implications of this? Anybody using code that writes code will have to change their security settings to trust access to the VBA project.
ASKER
Hi Imnorie.
I agree that it sounds a little weird, but the endgoal for this is quite important.
For many years my department have been developing different types of Excel reconciliation sheets that have been used in relation with bookkeeping jobs at client sites.
These Excel workbooks have not been protected and have not been standardized in any way.
I have created a template, that password protected the sheet, and secured that employees with little Excel experience did not include figures outside areas that sums figures etc. etc.
I was then asked to create a similar template that used 2 areas of cells that was formatted differently. I used the general code to have a standard to insert lines, and then used a button for each area to create the lines in the different areas.
I was then asked if I could try to generate a general template that could be used to build all future templates in our department, and we can have a variety of areas where we should insert lines
This is the background for the wish to insert a procedure, as I want to give the templatebuilders that possibility, but they can not moderate any code themselves. They are ordinary Excel users but skilled bookkeepers.
regards
Jørgen
I agree that it sounds a little weird, but the endgoal for this is quite important.
For many years my department have been developing different types of Excel reconciliation sheets that have been used in relation with bookkeeping jobs at client sites.
These Excel workbooks have not been protected and have not been standardized in any way.
I have created a template, that password protected the sheet, and secured that employees with little Excel experience did not include figures outside areas that sums figures etc. etc.
I was then asked to create a similar template that used 2 areas of cells that was formatted differently. I used the general code to have a standard to insert lines, and then used a button for each area to create the lines in the different areas.
I was then asked if I could try to generate a general template that could be used to build all future templates in our department, and we can have a variety of areas where we should insert lines
This is the background for the wish to insert a procedure, as I want to give the templatebuilders that possibility, but they can not moderate any code themselves. They are ordinary Excel users but skilled bookkeepers.
regards
Jørgen
Jørgen
They can't moderate any code themselves but they are allowed to run a procedure that inserts code?
Anyway, back to the actual problem.
Is it always the same code/sub you want to insert or will it differ slightly?
I ask because what you could do is import the code from a file.
They can't moderate any code themselves but they are allowed to run a procedure that inserts code?
Anyway, back to the actual problem.
Is it always the same code/sub you want to insert or will it differ slightly?
I ask because what you could do is import the code from a file.
ASKER
Hi Imnorie,
My intention was to build the procedure so they started stating the three arguments (sheetname, insertline name and top of area) into a userform, and then they should run the same code that will insert the "insert line procedure" with those 3 arguments
Is it easier to import the code from a file (I must admit, that I have not tried any of these possibilities before)?
regards
Jørgen
My intention was to build the procedure so they started stating the three arguments (sheetname, insertline name and top of area) into a userform, and then they should run the same code that will insert the "insert line procedure" with those 3 arguments
Is it easier to import the code from a file (I must admit, that I have not tried any of these possibilities before)?
regards
Jørgen
Jørgen
Is this only one sub, the one you posted in the first post?
Is this only one sub, the one you posted in the first post?
ASKER
Imnorie
Yes it is the insert_line procedure and followed by inserting the procedure
ActiveSheet.Unprotect "password"
Call Insert_Line("Sheet1", "Account_line", "Top_of_Account")
ActiveSheet.Protect "password
where the following variables
Sheet1
Account_line
Top_of_Account
should be stated in the userform and inserted to my procedure on the fly.
regards
Jørgen
Yes it is the insert_line procedure and followed by inserting the procedure
ActiveSheet.Unprotect "password"
Call Insert_Line("Sheet1", "Account_line", "Top_of_Account")
ActiveSheet.Protect "password
where the following variables
Sheet1
Account_line
Top_of_Account
should be stated in the userform and inserted to my procedure on the fly.
regards
Jørgen
ASKER
Hi Imnorie,
Was this impossible, or did you not have time for looking further?
regards
Jørgen
Was this impossible, or did you not have time for looking further?
regards
Jørgen
Jorgen
I've not really had time but I've just taken another look and saw your post that mentioned a userform.
Where does that come into things?
I've not really had time but I've just taken another look and saw your post that mentioned a userform.
Where does that come into things?
ASKER
Imnorie,
My intention was to activate a button, that created a new VBA code, and the userform should grab the3 variables needed (the user has to key the named ranges in) for this to run.
regards
Jørgen
My intention was to activate a button, that created a new VBA code, and the userform should grab the3 variables needed (the user has to key the named ranges in) for this to run.
regards
Jørgen
Jorgen
So you would have a userform in every workbook?
So you would have a userform in every workbook?
ASKER
Imnorie
Yes as this would be part of the template, that will be used to generate all future financial templates with.
regards
Jørgen
Yes as this would be part of the template, that will be used to generate all future financial templates with.
regards
Jørgen
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Imnorie,
That seems to work. Thanks a lot
Jørgen
That seems to work. Thanks a lot
Jørgen
In your code I really don't understand what you are trying to create a code module via code. Why not do it at design time? Also in that code you have On Error Resume Next. Try removing it and if you get any errors, tell us what they are.