469,357 Members | 1,637 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,357 developers. It's quick & easy.

Subscript out of range error 9

This is a wierd issue that i'm having. When I run the code without having vba open I get the error 9 message, but when I open vba and run the code it runs just fine. Here is the code

Expand|Select|Wrap|Line Numbers
  1. Public Sub preformat()
  2. 'Application.ScreenUpdating = False
  3.     Workbooks.Add
  4.     Do
  5.         If InStr(1, ActiveWorkbook.Name, "Book") > 0 Then Exit Do
  6.         ActiveWindow.ActivateNext
  7.     Loop
  8.     Columns("B:B").NumberFormat = "@"
  9.     Columns("D:E").NumberFormat = "0.000"
  10.     Columns("H:J").HorizontalAlignment = xlCenter
  11.     Range("A1").Select
  12.     'This procedure sets up the workbook procedure
  13. With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).codemodule
  14.     'Add the empty procedure
  15.     .InsertLines 1, "Private Sub Worksheet_SelectionChange(ByVal target As Range)"
  16.     .InsertLines 2, "If Target.Column < 8 Or Target.Column > 10 then Exit Sub"
  17.     .InsertLines 3, "If ActiveCell = """" Then"
  18.     .InsertLines 4, vbTab & "ActiveCell = ""X"""
  19.     .InsertLines 5, "ElseIf ActiveCell = ""X"" Then"
  20.     .InsertLines 6, vbTab & "ActiveCell = """
  21.     .InsertLines 7, "End If"
  22.     .InsertLines 8, "End Sub"
  23. End With
  24. 'Application.ScreenUpdating = True
  25. End Sub
  26.  
it throws the code at this line
Expand|Select|Wrap|Line Numbers
  1. With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.CodeName).codemodule
  2.  
any suggestions?
thx,
Rodney
Nov 2 '11 #1
1 2230
Killer42
8,435 Expert 8TB
I'd recommend you copy and paste this question into the Access / VBA forum where you'll find a lot more VBA expertise.
Dec 18 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Lukelrc | last post: by
2 posts views Thread by kscdavefl | last post: by
4 posts views Thread by Sheldon | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.