How To add coding to a new workbook

[robcarr]

Dear All,

I need the coding below to be available only when the user opens a certain book, the workbook the user opens is created by myself using VBA on a daily basis, is there away to add this code to the sheet1 module for the workbook I create or is there an easier way to do this.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column <> 5 Then
Exit Sub
Else
Select Case UCase(Target.Text)
Case &quot;RICH&quot;
Rows(Target.Row).Interior.ColorIndex = 37
Case &quot;SCOTT&quot;
Rows(Target.Row).Interior.ColorIndex = 6
Case &quot;MATT B&quot;
Rows(Target.Row).Interior.ColorIndex = 39
Case &quot;MARK&quot;
Rows(Target.Row).Interior.ColorIndex = 35
Case &quot;MANDY&quot;
Rows(Target.Row).Interior.ColorIndex = 40
Case &quot;TORI&quot;
Rows(Target.Row).Interior.ColorIndex = 38
Case &quot;&quot;
Rows(Target.Row).Interior.ColorIndex = 0
Case Else
End Select
End If
End Sub

any help is greatly appreciated.




Thanks Rob.

 

[Loomah]

Hi Rob
I've never done this myself but as always Chip Pearson has the solution (possibly)

http://www.cpearson.com/excel/vbe.htm

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[robcarr]

i found a solution, on this site, and I think it will work.

http://www.excel-center.com/mailing/028198h.htm

thanks for the suggestion

Thanks Rob.

 

[kphu]

Hi Rob,

I'm not sure whether your referenceing word or excel but yes its easy to move the code over.

Cut code from you current source then paste it in something like notepad to ensure that you don't lose it.

Open the excel or word file you wish to use it only in.

Open the VB editor.

If excel, in the vb editor. Open &quot;project Explorer&quot; (View -> &quot;Project Explorer&quot or ctrl + r. In the Project Explorer tab Click the + sign next to the name VBAPrject(name of file you want to store it in). Click the + sign of folder called Microsoft Excel Projects. Double click the Sheet# and then paste your code on the right hand window. If you want to allow it to work in any sheet of the workbook then select the &quot;Thisworkbook&quot; sheet and paste the code there.

For word its pretty much the samething. Except under the project explorer window select the Project(Name of file you want to store in) and the rest of what I explained above should apply.

Let me know if you have any questions.

Ken

 

[robcarr]

I know how to do this manually, the process you have advised, i wanted to do this while running a macro using coding,

Thanks Rob.

 

[robcarr]

I have progressed this far using chip pearsons help, what i would like to do is get the coding to work in any sheet in the workbook, by adding it to a standard module, as I can't work out the sheet module coding.

Here is the coding I want to work in any sheet in a workbook, but be placed in a standard module

Private Sub Workbook_Sheet_Change(ByVal Sh As Object, ByVal Source As Range)
If Target.Column <> 5 Then
Exit Sub
Else
Select Case UCase(Target.Text)
Case &quot;RICH&quot;
Rows(Target.Row).Interior.ColorIndex = 37
Case &quot;SCOTT&quot;
Rows(Target.Row).Interior.ColorIndex = 6
Case &quot;MATT B&quot;
Rows(Target.Row).Interior.ColorIndex = 39
Case &quot;MARK&quot;
Rows(Target.Row).Interior.ColorIndex = 35
Case &quot;MANDY&quot;
Rows(Target.Row).Interior.ColorIndex = 40
Case &quot;TORI&quot;
Rows(Target.Row).Interior.ColorIndex = 38
Case &quot;&quot;
Rows(Target.Row).Interior.ColorIndex = 0
Case Else
End Select
End If
End Sub

i dont get any errors, it just doesn't do anyhting.

Thanks for any help on this.

Thanks Rob.

 

[Loomah]

Hi Rob
Not sure what you mean now!
Looks like you have it but not quite. I don't think you can have your code in a standard module (what would fire it?) but it could go in the Workbook_SheetChange, eg

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox Sh.Name & Target.Address
End Sub

Like I said, looks like you were almost there!

Another thing about the code you posted that I've noticed is that you have referred to 'Target' without it being declared. Try putting your code in the sheet change event of the workbook to see if that works.

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[robcarr]

i have finally figured it out here is some coding to add a module to a new workbook using vba, the coding is added to thisworkbook.

Sub test()
Dim StartLine As Long
With ActiveWorkbook.VBProject.VBComponents(&quot;ThisWorkbook&quot.CodeModule
StartLine = 1
.InsertLines StartLine, _
&quot;Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)&quot; & Chr(13) & _
&quot; If Target.Column <> 5 Then&quot; & Chr(13) & _
&quot;Exit Sub&quot; & Chr(13) & _
&quot;Else&quot; & Chr(13) & _
&quot;Select Case UCase(Target.Text)&quot; & Chr(13) & _
&quot;Case &quot;&quot;RICH&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 37&quot; & Chr(13) & _
&quot;Case &quot;&quot;SCOTT&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 6&quot; & Chr(13) & _
&quot;Case &quot;&quot;MATT B&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 39&quot; & Chr(13) & _
&quot;Case &quot;&quot;MARK&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 35&quot; & Chr(13) & _
&quot;Case &quot;&quot;MANDY&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 40&quot; & Chr(13) & _
&quot;Case &quot;&quot;TORI&quot;&quot;&quot; & Chr(13) & _
&quot;Rows(Target.Row).Interior.ColorIndex = 38&quot; & Chr(13) & _
&quot;Case Else&quot; & Chr(13) & _
&quot;End Select&quot; & Chr(13) & _
&quot;End If&quot; & Chr(13) & _
&quot;End Sub&quot;
End With
End Sub

thought i would let you know how I got on.



Thanks Rob.