?
Solved

Inserting a procedure using VBA code in Excel

Posted on 2012-08-27
16
Medium Priority
?
946 Views
Last Modified: 2012-10-09
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).
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 Line

Open in new window


ActiveSheet.Unprotect "password"
Call Insert_Line("Sheet1", "Account_line", "Top_of_Account")
ActiveSheet.Protect "password"

Open in new window


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 Sub

Open in new window


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
0
Comment
Question by:Jorgen
  • 8
  • 7
16 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38336566
There's a problem with the original code and that is that while you pass it SheetName, "Sheet1" is hard-coded in the procedure.

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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38336576
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.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38339553
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
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 35

Expert Comment

by:Norie
ID: 38340640
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.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38341179
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 38341209
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.
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38342283
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 38342531
Jørgen

Is this only one sub, the one you posted in the first post?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38342698
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
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38431683
Hi Imnorie,

Was this impossible, or did you not have time for looking further?

regards

Jørgen
0
 
LVL 35

Expert Comment

by:Norie
ID: 38431770
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?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38431803
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
0
 
LVL 35

Expert Comment

by:Norie
ID: 38431824
Jorgen

So you would have a userform in every workbook?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38432360
Imnorie

Yes as this would be part of the template, that will be used to generate all future financial templates with.

regards

Jørgen
0
 
LVL 35

Accepted Solution

by:
Norie earned 2000 total points
ID: 38438463
Jorgen

Why don't you just put the code in the userform module and run it from the userform?
0
 
LVL 4

Author Comment

by:Jorgen
ID: 38476436
Hi Imnorie,

That seems to work. Thanks a lot

Jørgen
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question