| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subscript out of range (Error 9)
Hi folks I have a macro run for a form combo box, all works OK I wish to use at vb combo box, have placed my code inside to run on change . All runs, I copy my template spreadsheet and go to rename it based on the date selected from my combo box, the rename fails, error 9 Sdate = Format(cmbDate.Value, "dd mm") ' append name of sheet based on choice of day or night roster If Sheets("menu").optDay.Value = True Then ' Day Roster ' Day roster Sname = Sdate & "-D" Else ' night roster Sname = Sdate & "-N" End If ' check if sheet by that name exists, if false proceed If SheetExists(Sname) = False Then Sheets("Menu").Select If Sheets("menu").optDay.Value = True Then ' if Day Roster, copy day roster template Sheets("Template").Visible = True Sheets("Template").Select Sheets("Template").Copy Befo=Sheets(2) Sheets("Template (2)").Select <= Error 9 HERE Sheets("Template (2)").Name = Sname I think I have a hint of the problem, is it the () characters? Any fix possible? thanks John Images of home (NZ) http://www.titahi-bay.co.nz/home What we are up to in the UK http://www.titahi-bay.co.nz |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Subscript out of range (Error 9)
The new sheet (copy) will be the activesheet, so you don't need to select
it. You can refer to it as the activesheet Sdate = Format(cmbDate.Value, "dd mm") ' append name of sheet based on choice of day or night roster If Sheets("menu").optDay.Value = True Then ' Day Roster ' Day roster Sname = Sdate & "-D" Else ' night roster Sname = Sdate & "-N" End If ' check if sheet by that name exists, if false proceed If SheetExists(Sname) = False Then Sheets("Menu").Select If Sheets("menu").optDay.Value = True Then ' if Day Roster, Sheets("Template").Visible = True Sheets("Template").Select Sheets("Template").Copy Befo=Sheets(2) Activesheet.Name = Sname -- Regards, Tom Ogilvy "John in Surrey" wrote in message ... Hi folks I have a macro run for a form combo box, all works OK I wish to use at vb combo box, have placed my code inside to run on change . All runs, I copy my template spreadsheet and go to rename it based on the date selected from my combo box, the rename fails, error 9 Sdate = Format(cmbDate.Value, "dd mm") ' append name of sheet based on choice of day or night roster If Sheets("menu").optDay.Value = True Then ' Day Roster ' Day roster Sname = Sdate & "-D" Else ' night roster Sname = Sdate & "-N" End If ' check if sheet by that name exists, if false proceed If SheetExists(Sname) = False Then Sheets("Menu").Select If Sheets("menu").optDay.Value = True Then ' if Day Roster, copy day roster template Sheets("Template").Visible = True Sheets("Template").Select Sheets("Template").Copy Befo=Sheets(2) Sheets("Template (2)").Select <= Error 9 HERE Sheets("Template (2)").Name = Sname I think I have a hint of the problem, is it the () characters? Any fix possible? thanks John Images of home (NZ) http://www.titahi-bay.co.nz/home What we are up to in the UK http://www.titahi-bay.co.nz |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Subscript out of range error | Excel Programming | |||
| Subscript out of range error | Excel Discussion (Misc queries) | |||
| Type Mismatch error & subscript out of range error | Excel Programming | |||
| subscript out of range error | Excel Programming | |||
| Subscript out of range error | Excel Programming | |||
Linear Mode
