[RESOLVED] Include An Excel Macro in VB.net Code

[gbhs]

I have created a workbook template that will be used to collect some data from users.
I want to restrict user input to numbers between 0 and 20 in each worksheet range A1:C5 (say).
I know I can use a Macro like below to do it

With oSheet
Dim r As Integer = ds.Tables("tblStudents").Rows.Count + 1
Dim UsedRange As Excel.Range = .range("F2:K" & r)
For Each cell In UsedRange
If cell.Value < 0 or cell.Value > 20 Then
MsgBox(" Out of range")
cell.Value = ""
End If
Next
End With

Problem:
How can I include this macro to each worksheet when creating the workbook in my VB code.
Thanks

[Gruff]

Not sure why you would use a macro.

You select the cells to affect.

1) On the main menu under 'Data' you select 'Validation'.
2) Select whole number from the drop down.
3) Then enter the range of whole numbers to allow.
4) 0 - 20.
5) Add a messge if you want.

Save the workbook as a template.

Done.

[gbhs]

Hi Gruff
I dont want that each time I create a workbook for a user I should do what you propose above . This is because the worksheets of each user will be different but working cell range of each sheet depends on a certain dataset(as per my vb code) . So I want that when the workbook and named worksheets are created (this I have done successfuly) for a given user then the following event should be associated to each sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1:C5")) Is Nothing Then

If IsNumeric(Target.Value) Then

If Target.Value < 0 Or Target.Value > 20 Then
MsgBox ("Out of range")
Target = ""
Target.Activate
End If

If Target.Value >= 0 And Target.Value < 10 Then
Target.Font.ColorIndex = 3 'red font
End If
If Target.Value >= 10 And Target.Value <= 20 Then
Target.Font.ColorIndex = 5 'blue font
End If
Target.Font.Bold = True

Else
MsgBox ("Only numbers are allowed.")
Target = ""
Target.Activate

End If


End If

End Sub

The range A1:C5 is just a sample range I used to test this event
So how can I include this event in VB code (to be imported to each ceated sheet)

I need something similar to this
http://www.vbforums.com/showthread.p...et-from-vb-net


Thanks
Forgot to mention I'm a VB Newbie.

[vbfbryce]

Is your main issue the fact that A1:C5 is variable, and you don't know how to find the "boundaries" each time?

[gbhs]

No! vbfbryce
The main issue is that:
suppose I have an event like the one above in a text editor.
I want vb net to attach this event to sheet1 (say) when vb net creates the workbook.
The number of worksheets and their names depend on my vb application parameters.
The problem is to get this code from text editor and "paste" into each worksheet.

I have a loop that creates the worksheets .I think the code to paste the event
should be included in that loop.
Do you know of any code snippet?
Her's what I have

'START A NEW WORKBOOK IN EXCEL.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add

'CREATE WORKSHEETS FOR CHOSEN CLASSES
For j As Integer = 0 To dgvClasses.Rows.Count - 1
If dgvClasses.Rows(j).Cells("chk").Value = True Then
oSheet = oBook.Worksheets.Add(After:=oBook.Worksheets(oBook.Worksheets.Count))
oSheet.Name = dgvClasses.Rows(j).Cells(2).Value

'PUT COLUMN HEADERS IN EACH SHEET
oSheet.Range("A1").Value = "StudentID"
oSheet.Range("B1").Value = "RollNo"
oSheet.Range("C1").Value = "StudentNames"
oSheet.Range("D1").Value = "Sex"
oSheet.Range("E1").Value = "Rep"
oSheet.Range("F1").Value = "Seq1"
oSheet.Range("G1").Value = "Seq2"
oSheet.Range("H1").Value = "Seq3"
oSheet.Range("I1").Value = "Seq4"
oSheet.Range("J1").Value = "Seq5"
oSheet.Range("K1").Value = "Seq6"

oSheet.Range("A1:K1").Font.Bold = True
oSheet.Range("A1:K1").VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
oSheet.Range("A1:K1").Interior.Color = Excel.XlRgbColor.rgbYellow

'GET STUDENTS FROM DATABASE AND PUT
Dim qry As String
qry = "SELECT StudentID ,RollNo ,StudentNames ,Gender Sex,Repeater Rep "
qry += "FROM tblStudents WHERE ClassID =" & dgvClasses.Rows(j).Cells(1).Value & " ORDER BY RollNo "
Dim da As New SqlDataAdapter(qry, Conn)
Dim ds As New DataSet
da.Fill(ds, "tblStudents")

'ADD STUDENTS TO THIS osheet
For i = 0 To ds.Tables("tblStudents").Rows.Count - 1
For n = 0 To ds.Tables("tblStudents").Columns.Count - 1
oSheet.Cells(i + 2, n + 1) = ds.Tables("tblStudents").Rows(i)(n).ToString()
'i+2 means start from 2nd row--- n+1 means start from 1st col where n=0
Next
Next





'HIDE UNUSED ROWS AND COLS
With oSheet
Dim lastRow As Long
Dim lastCol As Long

Dim Rows As Integer = ds.Tables("tblStudents").Rows.Count + 1
Dim Columns As Integer = 12

lastRow = .Range("A" & Rows).End(Excel.XlDirection.xlDown).Row
lastCol = .Range("M" & "1").End(Excel.XlDirection.xlToRight).Column

.Range(.Cells(1, lastCol), .Cells(1, Columns)).EntireColumn.Hidden = True
.Range(lastRow & ":" & Rows).EntireRow.Hidden = True


'ADD WORKSHEET EVENT THAT WILL RESTRICT USER ENTRY IN THIS osheet
'(THIS IS WHERE I AM LOST)

'Dim MyMacro As String
'MyMacro = "Private Sub Worksheet_Change(ByVal Target As Range) "
'MyMacro += " If Not Intersect(Target, Range("A1:C5")) Is Nothing Then "
'MyMacro += " If IsNumeric(Target.Value) Then"
'-------------
'-------------

'OR how can I do something like
'oExcel.VBE.ActiveVBProject.vbcomponents.import("C:\Users\myPC\Desktop \TestEvent.txt")




End With


Any Help?

[vbfbryce]

No, I don't know of any way to incorporate new vba code into new sheets via code...but I believe you could handle it by putting validation and conditional formatting in via code when the new sheets are created. Would that be a suitable alternative? Are you wanting to do this all via VBA as opposed to .Net?

[TnTinMN]

Removed by author due to security concerns.

[gbhs]

It works great.
After adding the following:

Imports Microsoft.Vbe.Interop
Imports Excel = Microsoft.Office.Interop.Excel
Thanks to you guys for the help.
Hope this helps someone too

[vbfbryce]

Tn, how can I adapt that to do it from within Excel, specifically the part about setting trust for the VBA project object model?

[RobDog888]

You really shouldnt be changing the registry setting for Excel's Trust as its bypassing Excel's security which leaves the door open to running macro code without the users consent etc. Setting the Trust Access to the VBA project object models is a user checkbox in the Trust Center dialog.

[TnTinMN]

Tn, how can I adapt that to do it from within Excel, specifically the part about setting trust for the VBA project object model?

I don't know how you could do that from within Excel VBA as Excel appears to read that key when the application starts. I also highly doubt that there is any VBA code method within an executing macro to do it as that would circumvent Excels security. This technique works because then VB.Net code is controlling the environment before the application is started. The method I showed is really just a hack that can be used to bulk correct code in existing files.

I was not thinking to clearly last night when I posted that answer. It does exactly what the OP wants it to do, but since the OP is creating new WorkBooks based off a template, the better route would to be to create a template sheet with the code and copy that one to create new worksheets with the code. This technique does not required elevated access to the VBE object model.