VB Delete Sheets
 

Hello,
I am a VB Novice and need help.

My code allows the creation and naming of worksheets
according to user input into a list. If the user updates
or adds to the list, I want the worksheets to be deleted
and replaced with the updated versions, by using only a
single menu item.

Because of the user input, I cannot specify a fixed array
of sheet names. How can I specify an array that depends
on the user input? (The list of sheet names specified by
the user is a named range, but the array command did not
work.)

I tried: Sheets(array(range("User_List"))).delete
I also tried: Sheets(array("User_List")).delete


Thanks!
LJones

VB Delete Sheets
 

LJ, try (untested)

on error resume next
dim oS as object
for each cell in Range("user_list")
if not isempty(cell) then
set oS = nothing
set oS = sheets(cell.value)
if oS is nothing then
msgbox "Sheet " & cell.value & " does not exist. Activity
halted"
exit sub
end if
end if
next
on error goto 0

application.displayalerts = false
For each cell in Range("User_list")
if not isempty(cell) then sheets(cell.value).delete
Next
application.displayalerts = true

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

Application.display
"LJones" wrote in message
...
Hello,
I am a VB Novice and need help.

My code allows the creation and naming of worksheets
according to user input into a list. If the user updates
or adds to the list, I want the worksheets to be deleted
and replaced with the updated versions, by using only a
single menu item.

Because of the user input, I cannot specify a fixed array
of sheet names. How can I specify an array that depends
on the user input? (The list of sheet names specified by
the user is a named range, but the array command did not
work.)

I tried: Sheets(array(range("User_List"))).delete
I also tried: Sheets(array("User_List")).delete


Thanks!
LJones

VB Delete Sheets
 

Application.DisplayAlerts = False
for each cell in Range("User_List)
sheets(cell.value).Delete
Next
Application.DisplayAlerts = True

or
Application.DisplayAlerts = False
varr = Range("UserList")
Sheets(application.Transpose(varr)).Delete
Application.DisplayAlerts = True

if userlist has more than 5461 cells, then this could cause a problem in
some versions of excel. However, I doubt that is the case.

--
Regards,
Tom Ogilvy

"LJones" wrote in message
...
Hello,
I am a VB Novice and need help.

My code allows the creation and naming of worksheets
according to user input into a list. If the user updates
or adds to the list, I want the worksheets to be deleted
and replaced with the updated versions, by using only a
single menu item.

Because of the user input, I cannot specify a fixed array
of sheet names. How can I specify an array that depends
on the user input? (The list of sheet names specified by
the user is a named range, but the array command did not
work.)

I tried: Sheets(array(range("User_List"))).delete
I also tried: Sheets(array("User_List")).delete


Thanks!
LJones

VB Delete Sheets
 

It worked! Thanks! I never considered this approach, got
too hung up on the "Array" statement.

-----Original Message-----
LJ, try (untested)

on error resume next
dim oS as object
for each cell in Range("user_list")
if not isempty(cell) then
set oS = nothing
set oS = sheets(cell.value)
if oS is nothing then
msgbox "Sheet " & cell.value & " does not
exist. Activity
halted"
exit sub
end if
end if
next
on error goto 0

application.displayalerts = false
For each cell in Range("User_list")
if not isempty(cell) then sheets(cell.value).delete
Next
application.displayalerts = true

Robert Flanagan
Macro Systems
Delaware, U.S. 302-234-9857
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros
for Excel

Application.display
"LJones" wrote in
message
...
Hello,
I am a VB Novice and need help.

My code allows the creation and naming of worksheets
according to user input into a list. If the user
updates
or adds to the list, I want the worksheets to be deleted
and replaced with the updated versions, by using only a
single menu item.

Because of the user input, I cannot specify a fixed
array
of sheet names. How can I specify an array that depends
on the user input? (The list of sheet names specified
by
the user is a named range, but the array command did not
work.)

I tried: Sheets(array(range("User_List"))).delete
I also tried: Sheets(array("User_List")).delete


Thanks!
LJones


.