Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / April 2007

Tip: Looking for answers? Try searching our database.

Display current sheet name being processed in macro

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Ann - 17 Apr 2007 10:30 GMT
I have the following macro that will protect each sheet in a workbook.

For n = 1 To Sheets.Count
   'Sheets(n).Protect Password:="&PasswordEntered"
   Title = "Protecting sheet"
   Style = vbInformation
   MsgBox "Sheet Protected ", Style, Title
Next n

I need to display the sheet name back to the user, can someone suggest how I
do that within the macro along with the msgbox?

Signature

Thank U and Regards

Ann

Reply to this Message
Mike - 17 Apr 2007 10:48 GMT
Try your macro altered like this:-

Sub protect()
For n = 1 To Sheets.Count
   Sheets(n).protect 'Password:="&PasswordEntered"
   
   Title = "Protecting sheet"
   Style = vbInformation
   Message = ActiveSheet.Name & " Protected"
   MsgBox Message, Style, Title
Next n
End Sub

Mike

> I have the following macro that will protect each sheet in a workbook.
>
[quoted text clipped - 7 lines]
> I need to display the sheet name back to the user, can someone suggest how I
> do that within the macro along with the msgbox?
Reply to this Message
Ann - 17 Apr 2007 11:28 GMT
Mike,

I copied your code and it displays the Active Sheet Name (say sheet1) every
time.

I want the macro to display the sheet name as it goes through the workbook.

Any ideas?
Signature

Thank U and Regards

Ann

> Try your macro altered like this:-
>
[quoted text clipped - 22 lines]
> > I need to display the sheet name back to the user, can someone suggest how I
> > do that within the macro along with the msgbox?
Reply to this Message
Mike - 17 Apr 2007 10:50 GMT
OOPS missed a line

Sub protect()
For n = 1 To Sheets.Count
   
   Sheets(n).protect 'Password:="&PasswordEntered"
   Sheets(n).Select
   Title = "Protecting sheet"
   Style = vbInformation
   Message = ActiveSheet.Name & " Protected"
   MsgBox Message, Style, Title
Next n
End Sub

> I have the following macro that will protect each sheet in a workbook.
>
[quoted text clipped - 7 lines]
> I need to display the sheet name back to the user, can someone suggest how I
> do that within the macro along with the msgbox?
Reply to this Message
JE McGimpsey - 17 Apr 2007 12:40 GMT
One way:

   Dim n As Long
   For n = 1 To Sheets.Count
       With Sheets(n)
           .Protect Password:="&PasswordEntered"
           MsgBox Prompt:= .Name & " Protected", _
               Title:="Protecting Sheet", _
               Buttons:=vbInformation
       End WIth
   Next n

> I have the following macro that will protect each sheet in a workbook.
>
[quoted text clipped - 7 lines]
> I need to display the sheet name back to the user, can someone suggest how I
> do that within the macro along with the msgbox?
Reply to this Message
Ann - 17 Apr 2007 13:18 GMT
Mike, JE,

Thank U for your kind assistance.
Signature

Thank U and Regards

Ann

> One way:
>
[quoted text clipped - 19 lines]
> > I need to display the sheet name back to the user, can someone suggest how I
> > do that within the macro along with the msgbox?
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.