Lock worksheet, based on dropdown selection prior worksheet

Hi,

I am creating a workbook, where I have several worksheet that are labeled 
with the same first 7 characters "ab123 -".

The user selects one of these sheets from a dropdown in the first worksheet.
I provide a conditionally formatted hyperlink within worksheet A, based on 
their selection, but for quality control want an additional step that either 
locks an incorrect selection or presenting a msgbox to re-direct.

Can this be done?

Thanks
Michele
0
Michele (87)
6/19/2009 4:04:01 AM
excel.newusers 15348 articles. 2 followers. Follow

9 Replies
398 Views

Similar Articles

[PageSpeed] 45

What is conditionally formatted hyperlink?

How will Excel know if an incorrect selection has been made from the
dropdown?


Gord Dibben  MS Excel MVP

On Thu, 18 Jun 2009 21:04:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>Hi,
>
>I am creating a workbook, where I have several worksheet that are labeled 
>with the same first 7 characters "ab123 -".
>
>The user selects one of these sheets from a dropdown in the first worksheet.
>I provide a conditionally formatted hyperlink within worksheet A, based on 
>their selection, but for quality control want an additional step that either 
>locks an incorrect selection or presenting a msgbox to re-direct.
>
>Can this be done?
>
>Thanks
>Michele

0
Gord
6/19/2009 1:52:36 PM
Hi Gord,

The user selects a tab option from a list.
Examples would be:

AB123 Jones family
AB123 Smith family
AB123 Anderson family

In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
to each of the 3 tabs, that are formatted to 'appear' based on the selection 
from the list.

Ideally the user should use the hyperlink to access the correct tab in the 
workbook,but in real life...well we know what happens...they click the tab 
itself.

Should they select AB123 Jones family, I do not want them to be able to 
utilize the AB123 Smith/AB123 Anderson tabs via direct access.

I know I could conditionally format each worksheet to 'gray out' based on 
the initial selection, but am working within each with quite a bit of 
conditional formatting already, so I am trying to find another 'safety', such 
as a locked tab or a msgbox to re-direct.

I am not very savvy with writing my own macro code, but am trying to learn.

Thanks!


"Gord Dibben" wrote:

> What is conditionally formatted hyperlink?
> 
> How will Excel know if an incorrect selection has been made from the
> dropdown?
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
> <Michele@discussions.microsoft.com> wrote:
> 
> >Hi,
> >
> >I am creating a workbook, where I have several worksheet that are labeled 
> >with the same first 7 characters "ab123 -".
> >
> >The user selects one of these sheets from a dropdown in the first worksheet.
> >I provide a conditionally formatted hyperlink within worksheet A, based on 
> >their selection, but for quality control want an additional step that either 
> >locks an incorrect selection or presenting a msgbox to re-direct.
> >
> >Can this be done?
> >
> >Thanks
> >Michele
> 
> 
0
Michele (87)
6/19/2009 9:36:01 PM
Sounds like you want sheets hidden with only selected sheet from the list
becoming unhidden or is there another purpose behind limiting the access to
sheets only from the hyperlinks?

This would require VBA and some protection, which BTW is not that secure in
Excel.  How secure do you want this to be?

You could do away with the hyperlinks and just work directly off the
dropdown selection.

How many sheets do you have listed in your dropdown?

Would users be trusted to enable macros?


Gord

On Fri, 19 Jun 2009 14:36:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>Hi Gord,
>
>The user selects a tab option from a list.
>Examples would be:
>
>AB123 Jones family
>AB123 Smith family
>AB123 Anderson family
>
>In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
>to each of the 3 tabs, that are formatted to 'appear' based on the selection 
>from the list.
>
>Ideally the user should use the hyperlink to access the correct tab in the 
>workbook,but in real life...well we know what happens...they click the tab 
>itself.
>
>Should they select AB123 Jones family, I do not want them to be able to 
>utilize the AB123 Smith/AB123 Anderson tabs via direct access.
>
>I know I could conditionally format each worksheet to 'gray out' based on 
>the initial selection, but am working within each with quite a bit of 
>conditional formatting already, so I am trying to find another 'safety', such 
>as a locked tab or a msgbox to re-direct.
>
>I am not very savvy with writing my own macro code, but am trying to learn.
>
>Thanks!
>
>
>"Gord Dibben" wrote:
>
>> What is conditionally formatted hyperlink?
>> 
>> How will Excel know if an incorrect selection has been made from the
>> dropdown?
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>> 
>> >Hi,
>> >
>> >I am creating a workbook, where I have several worksheet that are labeled 
>> >with the same first 7 characters "ab123 -".
>> >
>> >The user selects one of these sheets from a dropdown in the first worksheet.
>> >I provide a conditionally formatted hyperlink within worksheet A, based on 
>> >their selection, but for quality control want an additional step that either 
>> >locks an incorrect selection or presenting a msgbox to re-direct.
>> >
>> >Can this be done?
>> >
>> >Thanks
>> >Michele
>> 
>> 

0
Gord
6/20/2009 2:40:46 PM
Yes!!  

That's it exactly...I would like only the selected sheet unhidden, based on 
the dropdown...stuff the rest in the closet :-)

This is a workbook used by about 8 team members, all within my department.  

We have been working from 12 different workbooks (each contained 4 
worksheets that were identical, and the ABC123 xxx that was different) just 
due to the differences.  By using formulas based off user input in specific 
cells, I have been able to combine all 12 workbooks into 1 workbook with the 
4 identical tabs and then the 4 different ABC123 xxx tabs.

There are currently quality issues with work being completed in the 
incorrect workbook. 

The dropdown currently contains 4 selections, with the potential to expand 
to 6 if I incorporate for another 2 person team.

As far as the users being trusted to enable the macros, part of the training 
on using the new workbook will be to stress the importance of doing so.  They 
have been asking for something like this, so I do not think it will be an 
issue.  It's a conscious decision on their part, and they will be accountable 
if the QA team were to see all tabs.  

This is why I posted in new users...I have some good working knowledge of 
formulas, but am basically a VBA virgin (that is I have recorded some macros 
and have been able to edit as needed).

Thanks~
Michele

"Gord Dibben" wrote:

> Sounds like you want sheets hidden with only selected sheet from the list
> becoming unhidden or is there another purpose behind limiting the access to
> sheets only from the hyperlinks?
> 
> This would require VBA and some protection, which BTW is not that secure in
> Excel.  How secure do you want this to be?
> 
> You could do away with the hyperlinks and just work directly off the
> dropdown selection.
> 
> How many sheets do you have listed in your dropdown?
> 
> Would users be trusted to enable macros?
> 
> 
> Gord
> 
> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
> <Michele@discussions.microsoft.com> wrote:
> 
> >Hi Gord,
> >
> >The user selects a tab option from a list.
> >Examples would be:
> >
> >AB123 Jones family
> >AB123 Smith family
> >AB123 Anderson family
> >
> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
> >from the list.
> >
> >Ideally the user should use the hyperlink to access the correct tab in the 
> >workbook,but in real life...well we know what happens...they click the tab 
> >itself.
> >
> >Should they select AB123 Jones family, I do not want them to be able to 
> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
> >
> >I know I could conditionally format each worksheet to 'gray out' based on 
> >the initial selection, but am working within each with quite a bit of 
> >conditional formatting already, so I am trying to find another 'safety', such 
> >as a locked tab or a msgbox to re-direct.
> >
> >I am not very savvy with writing my own macro code, but am trying to learn.
> >
> >Thanks!
> >
> >
> >"Gord Dibben" wrote:
> >
> >> What is conditionally formatted hyperlink?
> >> 
> >> How will Excel know if an incorrect selection has been made from the
> >> dropdown?
> >> 
> >> 
> >> Gord Dibben  MS Excel MVP
> >> 
> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
> >> <Michele@discussions.microsoft.com> wrote:
> >> 
> >> >Hi,
> >> >
> >> >I am creating a workbook, where I have several worksheet that are labeled 
> >> >with the same first 7 characters "ab123 -".
> >> >
> >> >The user selects one of these sheets from a dropdown in the first worksheet.
> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
> >> >their selection, but for quality control want an additional step that either 
> >> >locks an incorrect selection or presenting a msgbox to re-direct.
> >> >
> >> >Can this be done?
> >> >
> >> >Thanks
> >> >Michele
> >> 
> >> 
> 
> 
0
Michele (87)
6/20/2009 3:32:01 PM
Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
contains a list of other worksheets.

Copy/paste these two events into the "Index" sheet module.

Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
        If sht.Name <> "Index" Then
            sht.Visible = xlVeryHidden
        End If
    Next sht
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
    On Error GoTo endit
    Application.EnableEvents = False
    With Sheets(Target.Value)
        .Visible = True
        .Activate
    End With
endit:
    Application.EnableEvents = True
End Sub

Right-click on the tab and "View Code" to access the module.

Edit to suit........"A1" or "Index" may not be correct for you.

Alt + q to return to the Excel window.

Select a name from the dropdown list to hide all sheets except "Index" and
selected sheet.

To bring up another sheet, switch back to "Index"


Gord





On Sat, 20 Jun 2009 08:32:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>Yes!!  
>
>That's it exactly...I would like only the selected sheet unhidden, based on 
>the dropdown...stuff the rest in the closet :-)
>
>This is a workbook used by about 8 team members, all within my department.  
>
>We have been working from 12 different workbooks (each contained 4 
>worksheets that were identical, and the ABC123 xxx that was different) just 
>due to the differences.  By using formulas based off user input in specific 
>cells, I have been able to combine all 12 workbooks into 1 workbook with the 
>4 identical tabs and then the 4 different ABC123 xxx tabs.
>
>There are currently quality issues with work being completed in the 
>incorrect workbook. 
>
>The dropdown currently contains 4 selections, with the potential to expand 
>to 6 if I incorporate for another 2 person team.
>
>As far as the users being trusted to enable the macros, part of the training 
>on using the new workbook will be to stress the importance of doing so.  They 
>have been asking for something like this, so I do not think it will be an 
>issue.  It's a conscious decision on their part, and they will be accountable 
>if the QA team were to see all tabs.  
>
>This is why I posted in new users...I have some good working knowledge of 
>formulas, but am basically a VBA virgin (that is I have recorded some macros 
>and have been able to edit as needed).
>
>Thanks~
>Michele
>
>"Gord Dibben" wrote:
>
>> Sounds like you want sheets hidden with only selected sheet from the list
>> becoming unhidden or is there another purpose behind limiting the access to
>> sheets only from the hyperlinks?
>> 
>> This would require VBA and some protection, which BTW is not that secure in
>> Excel.  How secure do you want this to be?
>> 
>> You could do away with the hyperlinks and just work directly off the
>> dropdown selection.
>> 
>> How many sheets do you have listed in your dropdown?
>> 
>> Would users be trusted to enable macros?
>> 
>> 
>> Gord
>> 
>> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>> 
>> >Hi Gord,
>> >
>> >The user selects a tab option from a list.
>> >Examples would be:
>> >
>> >AB123 Jones family
>> >AB123 Smith family
>> >AB123 Anderson family
>> >
>> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
>> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
>> >from the list.
>> >
>> >Ideally the user should use the hyperlink to access the correct tab in the 
>> >workbook,but in real life...well we know what happens...they click the tab 
>> >itself.
>> >
>> >Should they select AB123 Jones family, I do not want them to be able to 
>> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
>> >
>> >I know I could conditionally format each worksheet to 'gray out' based on 
>> >the initial selection, but am working within each with quite a bit of 
>> >conditional formatting already, so I am trying to find another 'safety', such 
>> >as a locked tab or a msgbox to re-direct.
>> >
>> >I am not very savvy with writing my own macro code, but am trying to learn.
>> >
>> >Thanks!
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> What is conditionally formatted hyperlink?
>> >> 
>> >> How will Excel know if an incorrect selection has been made from the
>> >> dropdown?
>> >> 
>> >> 
>> >> Gord Dibben  MS Excel MVP
>> >> 
>> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
>> >> <Michele@discussions.microsoft.com> wrote:
>> >> 
>> >> >Hi,
>> >> >
>> >> >I am creating a workbook, where I have several worksheet that are labeled 
>> >> >with the same first 7 characters "ab123 -".
>> >> >
>> >> >The user selects one of these sheets from a dropdown in the first worksheet.
>> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
>> >> >their selection, but for quality control want an additional step that either 
>> >> >locks an incorrect selection or presenting a msgbox to re-direct.
>> >> >
>> >> >Can this be done?
>> >> >
>> >> >Thanks
>> >> >Michele
>> >> 
>> >> 
>> 
>> 

0
Gord
6/20/2009 4:45:05 PM
Dropdown was in E5 of "Index"...though I must not have done this correctly, 
since it hid ALL of my worksheets, leaving only the last one...which btw was 
very slick, even though not quite what I wanted :-)  Perhaps I got a tad 
confused because you mentioned A1 but the code below references Range "H1"?

There are worksheets that I need to remain visible (there are several that 
are used in conjunction with the the ones I am trying to limit.  I need 
Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6, 
Sheet7 or Sheet8 depending on the dropdown.

"Gord Dibben" wrote:

> Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
> contains a list of other worksheets.
> 
> Copy/paste these two events into the "Index" sheet module.
> 
> Private Sub Worksheet_Activate()
> Dim sht As Worksheet
> For Each sht In ActiveWorkbook.Sheets
>         If sht.Name <> "Index" Then
>             sht.Visible = xlVeryHidden
>         End If
>     Next sht
> End Sub
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>  If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
>     On Error GoTo endit
>     Application.EnableEvents = False
>     With Sheets(Target.Value)
>         .Visible = True
>         .Activate
>     End With
> endit:
>     Application.EnableEvents = True
> End Sub
> 
> Right-click on the tab and "View Code" to access the module.
> 
> Edit to suit........"A1" or "Index" may not be correct for you.
> 
> Alt + q to return to the Excel window.
> 
> Select a name from the dropdown list to hide all sheets except "Index" and
> selected sheet.
> 
> To bring up another sheet, switch back to "Index"
> 
> 
> Gord
> 
> 
> 
> 
> 
> On Sat, 20 Jun 2009 08:32:01 -0700, Michele
> <Michele@discussions.microsoft.com> wrote:
> 
> >Yes!!  
> >
> >That's it exactly...I would like only the selected sheet unhidden, based on 
> >the dropdown...stuff the rest in the closet :-)
> >
> >This is a workbook used by about 8 team members, all within my department.  
> >
> >We have been working from 12 different workbooks (each contained 4 
> >worksheets that were identical, and the ABC123 xxx that was different) just 
> >due to the differences.  By using formulas based off user input in specific 
> >cells, I have been able to combine all 12 workbooks into 1 workbook with the 
> >4 identical tabs and then the 4 different ABC123 xxx tabs.
> >
> >There are currently quality issues with work being completed in the 
> >incorrect workbook. 
> >
> >The dropdown currently contains 4 selections, with the potential to expand 
> >to 6 if I incorporate for another 2 person team.
> >
> >As far as the users being trusted to enable the macros, part of the training 
> >on using the new workbook will be to stress the importance of doing so.  They 
> >have been asking for something like this, so I do not think it will be an 
> >issue.  It's a conscious decision on their part, and they will be accountable 
> >if the QA team were to see all tabs.  
> >
> >This is why I posted in new users...I have some good working knowledge of 
> >formulas, but am basically a VBA virgin (that is I have recorded some macros 
> >and have been able to edit as needed).
> >
> >Thanks~
> >Michele
> >
> >"Gord Dibben" wrote:
> >
> >> Sounds like you want sheets hidden with only selected sheet from the list
> >> becoming unhidden or is there another purpose behind limiting the access to
> >> sheets only from the hyperlinks?
> >> 
> >> This would require VBA and some protection, which BTW is not that secure in
> >> Excel.  How secure do you want this to be?
> >> 
> >> You could do away with the hyperlinks and just work directly off the
> >> dropdown selection.
> >> 
> >> How many sheets do you have listed in your dropdown?
> >> 
> >> Would users be trusted to enable macros?
> >> 
> >> 
> >> Gord
> >> 
> >> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
> >> <Michele@discussions.microsoft.com> wrote:
> >> 
> >> >Hi Gord,
> >> >
> >> >The user selects a tab option from a list.
> >> >Examples would be:
> >> >
> >> >AB123 Jones family
> >> >AB123 Smith family
> >> >AB123 Anderson family
> >> >
> >> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
> >> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
> >> >from the list.
> >> >
> >> >Ideally the user should use the hyperlink to access the correct tab in the 
> >> >workbook,but in real life...well we know what happens...they click the tab 
> >> >itself.
> >> >
> >> >Should they select AB123 Jones family, I do not want them to be able to 
> >> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
> >> >
> >> >I know I could conditionally format each worksheet to 'gray out' based on 
> >> >the initial selection, but am working within each with quite a bit of 
> >> >conditional formatting already, so I am trying to find another 'safety', such 
> >> >as a locked tab or a msgbox to re-direct.
> >> >
> >> >I am not very savvy with writing my own macro code, but am trying to learn.
> >> >
> >> >Thanks!
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> What is conditionally formatted hyperlink?
> >> >> 
> >> >> How will Excel know if an incorrect selection has been made from the
> >> >> dropdown?
> >> >> 
> >> >> 
> >> >> Gord Dibben  MS Excel MVP
> >> >> 
> >> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
> >> >> <Michele@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >Hi,
> >> >> >
> >> >> >I am creating a workbook, where I have several worksheet that are labeled 
> >> >> >with the same first 7 characters "ab123 -".
> >> >> >
> >> >> >The user selects one of these sheets from a dropdown in the first worksheet.
> >> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
> >> >> >their selection, but for quality control want an additional step that either 
> >> >> >locks an incorrect selection or presenting a msgbox to re-direct.
> >> >> >
> >> >> >Can this be done?
> >> >> >
> >> >> >Thanks
> >> >> >Michele
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
Michele (87)
6/20/2009 10:12:01 PM
The "A1" was a typo.

You have an "Index" sheet and Sheet1 through 4 to remain visible at all
times?

Sheet5 through 8 will be hidden until selected from the dropdown?

User selects Sheet5 and it becomes visible.  When user finished with Sheet5,
switches back to "Index" sheet to select another sheet.......Sheet5 becomes
hidden.

Is that basically what you need?

In the following code, edit    Sheets(Array( sheetnames to suit.

Private Sub Worksheet_Activate()
Dim sht As Worksheet
For Each sht In Thisworkbook.Worksheets(Array _
("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
sht.Visible = xlVeryHidden
    Next sht
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("E5")) Is Nothing Then Exit Sub
    On Error GoTo endit
    Application.EnableEvents = False
    With Sheets(Target.Value)
        .Visible = True
        .Activate
    End With
endit:
    Application.EnableEvents = True
End Sub

One more thing..............do you want Sheets 5 through 8 hidden when the
workbook opens?

You could add this to your Thisworkbook module

Private Sub Workbook_Open()
    Dim sht As Worksheet
    For Each sht In ThisWorkbook.Worksheets(Array _
         ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
        sht.Visible = xlVeryHidden
    Next sht
End Sub

I know I'm overloading you but use as much or as little as you want

If too confusing, send your workbook to my email.

gorddibbATshawDOTca

Replace the AT and DOT


Gord

On Sat, 20 Jun 2009 15:12:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>Dropdown was in E5 of "Index"...though I must not have done this correctly, 
>since it hid ALL of my worksheets, leaving only the last one...which btw was 
>very slick, even though not quite what I wanted :-)  Perhaps I got a tad 
>confused because you mentioned A1 but the code below references Range "H1"?
>
>There are worksheets that I need to remain visible (there are several that 
>are used in conjunction with the the ones I am trying to limit.  I need 
>Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6, 
>Sheet7 or Sheet8 depending on the dropdown.
>
>"Gord Dibben" wrote:
>
>> Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
>> contains a list of other worksheets.
>> 
>> Copy/paste these two events into the "Index" sheet module.
>> 
>> Private Sub Worksheet_Activate()
>> Dim sht As Worksheet
>> For Each sht In ActiveWorkbook.Sheets
>>         If sht.Name <> "Index" Then
>>             sht.Visible = xlVeryHidden
>>         End If
>>     Next sht
>> End Sub
>> 
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>  If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
>>     On Error GoTo endit
>>     Application.EnableEvents = False
>>     With Sheets(Target.Value)
>>         .Visible = True
>>         .Activate
>>     End With
>> endit:
>>     Application.EnableEvents = True
>> End Sub
>> 
>> Right-click on the tab and "View Code" to access the module.
>> 
>> Edit to suit........"A1" or "Index" may not be correct for you.
>> 
>> Alt + q to return to the Excel window.
>> 
>> Select a name from the dropdown list to hide all sheets except "Index" and
>> selected sheet.
>> 
>> To bring up another sheet, switch back to "Index"
>> 
>> 
>> Gord
>> 
>> 
>> 
>> 
>> 
>> On Sat, 20 Jun 2009 08:32:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>> 
>> >Yes!!  
>> >
>> >That's it exactly...I would like only the selected sheet unhidden, based on 
>> >the dropdown...stuff the rest in the closet :-)
>> >
>> >This is a workbook used by about 8 team members, all within my department.  
>> >
>> >We have been working from 12 different workbooks (each contained 4 
>> >worksheets that were identical, and the ABC123 xxx that was different) just 
>> >due to the differences.  By using formulas based off user input in specific 
>> >cells, I have been able to combine all 12 workbooks into 1 workbook with the 
>> >4 identical tabs and then the 4 different ABC123 xxx tabs.
>> >
>> >There are currently quality issues with work being completed in the 
>> >incorrect workbook. 
>> >
>> >The dropdown currently contains 4 selections, with the potential to expand 
>> >to 6 if I incorporate for another 2 person team.
>> >
>> >As far as the users being trusted to enable the macros, part of the training 
>> >on using the new workbook will be to stress the importance of doing so.  They 
>> >have been asking for something like this, so I do not think it will be an 
>> >issue.  It's a conscious decision on their part, and they will be accountable 
>> >if the QA team were to see all tabs.  
>> >
>> >This is why I posted in new users...I have some good working knowledge of 
>> >formulas, but am basically a VBA virgin (that is I have recorded some macros 
>> >and have been able to edit as needed).
>> >
>> >Thanks~
>> >Michele
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Sounds like you want sheets hidden with only selected sheet from the list
>> >> becoming unhidden or is there another purpose behind limiting the access to
>> >> sheets only from the hyperlinks?
>> >> 
>> >> This would require VBA and some protection, which BTW is not that secure in
>> >> Excel.  How secure do you want this to be?
>> >> 
>> >> You could do away with the hyperlinks and just work directly off the
>> >> dropdown selection.
>> >> 
>> >> How many sheets do you have listed in your dropdown?
>> >> 
>> >> Would users be trusted to enable macros?
>> >> 
>> >> 
>> >> Gord
>> >> 
>> >> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
>> >> <Michele@discussions.microsoft.com> wrote:
>> >> 
>> >> >Hi Gord,
>> >> >
>> >> >The user selects a tab option from a list.
>> >> >Examples would be:
>> >> >
>> >> >AB123 Jones family
>> >> >AB123 Smith family
>> >> >AB123 Anderson family
>> >> >
>> >> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
>> >> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
>> >> >from the list.
>> >> >
>> >> >Ideally the user should use the hyperlink to access the correct tab in the 
>> >> >workbook,but in real life...well we know what happens...they click the tab 
>> >> >itself.
>> >> >
>> >> >Should they select AB123 Jones family, I do not want them to be able to 
>> >> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
>> >> >
>> >> >I know I could conditionally format each worksheet to 'gray out' based on 
>> >> >the initial selection, but am working within each with quite a bit of 
>> >> >conditional formatting already, so I am trying to find another 'safety', such 
>> >> >as a locked tab or a msgbox to re-direct.
>> >> >
>> >> >I am not very savvy with writing my own macro code, but am trying to learn.
>> >> >
>> >> >Thanks!
>> >> >
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> What is conditionally formatted hyperlink?
>> >> >> 
>> >> >> How will Excel know if an incorrect selection has been made from the
>> >> >> dropdown?
>> >> >> 
>> >> >> 
>> >> >> Gord Dibben  MS Excel MVP
>> >> >> 
>> >> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
>> >> >> <Michele@discussions.microsoft.com> wrote:
>> >> >> 
>> >> >> >Hi,
>> >> >> >
>> >> >> >I am creating a workbook, where I have several worksheet that are labeled 
>> >> >> >with the same first 7 characters "ab123 -".
>> >> >> >
>> >> >> >The user selects one of these sheets from a dropdown in the first worksheet.
>> >> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
>> >> >> >their selection, but for quality control want an additional step that either 
>> >> >> >locks an incorrect selection or presenting a msgbox to re-direct.
>> >> >> >
>> >> >> >Can this be done?
>> >> >> >
>> >> >> >Thanks
>> >> >> >Michele
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>> 

0
Gord
6/21/2009 2:51:25 AM
SWEET!!!

I used both the "Index" and the "Thisworkbook" code below, and this gives me 
99.9% of what I wanted :::does happy dance:::

I can train around the fact that while still in the workbook if they go back 
to Index, it re-hides the selected sheet (they would never use Sheets 5-8 at 
the same time, and they can just re-select on the dropdown) ~ what you have 
helped me with is a H-U-G-E gain for my team.

And you explained things so that this newbie actually understands (albeit 
dimmly) what the code is doing for me :-)

Thanks so much Gord ~
Michele

"Gord Dibben" wrote:

> The "A1" was a typo.
> 
> You have an "Index" sheet and Sheet1 through 4 to remain visible at all
> times?
> 
> Sheet5 through 8 will be hidden until selected from the dropdown?
> 
> User selects Sheet5 and it becomes visible.  When user finished with Sheet5,
> switches back to "Index" sheet to select another sheet.......Sheet5 becomes
> hidden.
> 
> Is that basically what you need?
> 
> In the following code, edit    Sheets(Array( sheetnames to suit.
> 
> Private Sub Worksheet_Activate()
> Dim sht As Worksheet
> For Each sht In Thisworkbook.Worksheets(Array _
> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
> sht.Visible = xlVeryHidden
>     Next sht
> End Sub
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
>     If Intersect(Target, Me.Range("E5")) Is Nothing Then Exit Sub
>     On Error GoTo endit
>     Application.EnableEvents = False
>     With Sheets(Target.Value)
>         .Visible = True
>         .Activate
>     End With
> endit:
>     Application.EnableEvents = True
> End Sub
> 
> One more thing..............do you want Sheets 5 through 8 hidden when the
> workbook opens?
> 
> You could add this to your Thisworkbook module
> 
> Private Sub Workbook_Open()
>     Dim sht As Worksheet
>     For Each sht In ThisWorkbook.Worksheets(Array _
>          ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
>         sht.Visible = xlVeryHidden
>     Next sht
> End Sub
> 
> I know I'm overloading you but use as much or as little as you want
> 
> If too confusing, send your workbook to my email.
> 
> gorddibbATshawDOTca
> 
> Replace the AT and DOT
> 
> 
> Gord
> 
> On Sat, 20 Jun 2009 15:12:01 -0700, Michele
> <Michele@discussions.microsoft.com> wrote:
> 
> >Dropdown was in E5 of "Index"...though I must not have done this correctly, 
> >since it hid ALL of my worksheets, leaving only the last one...which btw was 
> >very slick, even though not quite what I wanted :-)  Perhaps I got a tad 
> >confused because you mentioned A1 but the code below references Range "H1"?
> >
> >There are worksheets that I need to remain visible (there are several that 
> >are used in conjunction with the the ones I am trying to limit.  I need 
> >Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6, 
> >Sheet7 or Sheet8 depending on the dropdown.
> >
> >"Gord Dibben" wrote:
> >
> >> Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
> >> contains a list of other worksheets.
> >> 
> >> Copy/paste these two events into the "Index" sheet module.
> >> 
> >> Private Sub Worksheet_Activate()
> >> Dim sht As Worksheet
> >> For Each sht In ActiveWorkbook.Sheets
> >>         If sht.Name <> "Index" Then
> >>             sht.Visible = xlVeryHidden
> >>         End If
> >>     Next sht
> >> End Sub
> >> 
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >>  If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
> >>     On Error GoTo endit
> >>     Application.EnableEvents = False
> >>     With Sheets(Target.Value)
> >>         .Visible = True
> >>         .Activate
> >>     End With
> >> endit:
> >>     Application.EnableEvents = True
> >> End Sub
> >> 
> >> Right-click on the tab and "View Code" to access the module.
> >> 
> >> Edit to suit........"A1" or "Index" may not be correct for you.
> >> 
> >> Alt + q to return to the Excel window.
> >> 
> >> Select a name from the dropdown list to hide all sheets except "Index" and
> >> selected sheet.
> >> 
> >> To bring up another sheet, switch back to "Index"
> >> 
> >> 
> >> Gord
> >> 
> >> 
> >> 
> >> 
> >> 
> >> On Sat, 20 Jun 2009 08:32:01 -0700, Michele
> >> <Michele@discussions.microsoft.com> wrote:
> >> 
> >> >Yes!!  
> >> >
> >> >That's it exactly...I would like only the selected sheet unhidden, based on 
> >> >the dropdown...stuff the rest in the closet :-)
> >> >
> >> >This is a workbook used by about 8 team members, all within my department.  
> >> >
> >> >We have been working from 12 different workbooks (each contained 4 
> >> >worksheets that were identical, and the ABC123 xxx that was different) just 
> >> >due to the differences.  By using formulas based off user input in specific 
> >> >cells, I have been able to combine all 12 workbooks into 1 workbook with the 
> >> >4 identical tabs and then the 4 different ABC123 xxx tabs.
> >> >
> >> >There are currently quality issues with work being completed in the 
> >> >incorrect workbook. 
> >> >
> >> >The dropdown currently contains 4 selections, with the potential to expand 
> >> >to 6 if I incorporate for another 2 person team.
> >> >
> >> >As far as the users being trusted to enable the macros, part of the training 
> >> >on using the new workbook will be to stress the importance of doing so.  They 
> >> >have been asking for something like this, so I do not think it will be an 
> >> >issue.  It's a conscious decision on their part, and they will be accountable 
> >> >if the QA team were to see all tabs.  
> >> >
> >> >This is why I posted in new users...I have some good working knowledge of 
> >> >formulas, but am basically a VBA virgin (that is I have recorded some macros 
> >> >and have been able to edit as needed).
> >> >
> >> >Thanks~
> >> >Michele
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> Sounds like you want sheets hidden with only selected sheet from the list
> >> >> becoming unhidden or is there another purpose behind limiting the access to
> >> >> sheets only from the hyperlinks?
> >> >> 
> >> >> This would require VBA and some protection, which BTW is not that secure in
> >> >> Excel.  How secure do you want this to be?
> >> >> 
> >> >> You could do away with the hyperlinks and just work directly off the
> >> >> dropdown selection.
> >> >> 
> >> >> How many sheets do you have listed in your dropdown?
> >> >> 
> >> >> Would users be trusted to enable macros?
> >> >> 
> >> >> 
> >> >> Gord
> >> >> 
> >> >> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
> >> >> <Michele@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >Hi Gord,
> >> >> >
> >> >> >The user selects a tab option from a list.
> >> >> >Examples would be:
> >> >> >
> >> >> >AB123 Jones family
> >> >> >AB123 Smith family
> >> >> >AB123 Anderson family
> >> >> >
> >> >> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
> >> >> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
> >> >> >from the list.
> >> >> >
> >> >> >Ideally the user should use the hyperlink to access the correct tab in the 
> >> >> >workbook,but in real life...well we know what happens...they click the tab 
> >> >> >itself.
> >> >> >
> >> >> >Should they select AB123 Jones family, I do not want them to be able to 
> >> >> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
> >> >> >
> >> >> >I know I could conditionally format each worksheet to 'gray out' based on 
> >> >> >the initial selection, but am working within each with quite a bit of 
> >> >> >conditional formatting already, so I am trying to find another 'safety', such 
> >> >> >as a locked tab or a msgbox to re-direct.
> >> >> >
> >> >> >I am not very savvy with writing my own macro code, but am trying to learn.
> >> >> >
> >> >> >Thanks!
> >> >> >
> >> >> >
> >> >> >"Gord Dibben" wrote:
> >> >> >
> >> >> >> What is conditionally formatted hyperlink?
> >> >> >> 
> >> >> >> How will Excel know if an incorrect selection has been made from the
> >> >> >> dropdown?
> >> >> >> 
> >> >> >> 
> >> >> >> Gord Dibben  MS Excel MVP
> >> >> >> 
> >> >> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
> >> >> >> <Michele@discussions.microsoft.com> wrote:
> >> >> >> 
> >> >> >> >Hi,
> >> >> >> >
> >> >> >> >I am creating a workbook, where I have several worksheet that are labeled 
> >> >> >> >with the same first 7 characters "ab123 -".
> >> >> >> >
> >> >> >> >The user selects one of these sheets from a dropdown in the first worksheet.
> >> >> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
> >> >> >> >their selection, but for quality control want an additional step that either 
> >> >> >> >locks an incorrect selection or presenting a msgbox to re-direct.
> >> >> >> >
> >> >> >> >Can this be done?
> >> >> >> >
> >> >> >> >Thanks
> >> >> >> >Michele
> >> >> >> 
> >> >> >> 
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
Michele (87)
6/21/2009 6:53:01 PM
Good to hear.


Gord

On Sun, 21 Jun 2009 11:53:01 -0700, Michele
<Michele@discussions.microsoft.com> wrote:

>SWEET!!!
>
>I used both the "Index" and the "Thisworkbook" code below, and this gives me 
>99.9% of what I wanted :::does happy dance:::
>
>I can train around the fact that while still in the workbook if they go back 
>to Index, it re-hides the selected sheet (they would never use Sheets 5-8 at 
>the same time, and they can just re-select on the dropdown) ~ what you have 
>helped me with is a H-U-G-E gain for my team.
>
>And you explained things so that this newbie actually understands (albeit 
>dimmly) what the code is doing for me :-)
>
>Thanks so much Gord ~
>Michele
>
>"Gord Dibben" wrote:
>
>> The "A1" was a typo.
>> 
>> You have an "Index" sheet and Sheet1 through 4 to remain visible at all
>> times?
>> 
>> Sheet5 through 8 will be hidden until selected from the dropdown?
>> 
>> User selects Sheet5 and it becomes visible.  When user finished with Sheet5,
>> switches back to "Index" sheet to select another sheet.......Sheet5 becomes
>> hidden.
>> 
>> Is that basically what you need?
>> 
>> In the following code, edit    Sheets(Array( sheetnames to suit.
>> 
>> Private Sub Worksheet_Activate()
>> Dim sht As Worksheet
>> For Each sht In Thisworkbook.Worksheets(Array _
>> ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
>> sht.Visible = xlVeryHidden
>>     Next sht
>> End Sub
>> 
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>     If Intersect(Target, Me.Range("E5")) Is Nothing Then Exit Sub
>>     On Error GoTo endit
>>     Application.EnableEvents = False
>>     With Sheets(Target.Value)
>>         .Visible = True
>>         .Activate
>>     End With
>> endit:
>>     Application.EnableEvents = True
>> End Sub
>> 
>> One more thing..............do you want Sheets 5 through 8 hidden when the
>> workbook opens?
>> 
>> You could add this to your Thisworkbook module
>> 
>> Private Sub Workbook_Open()
>>     Dim sht As Worksheet
>>     For Each sht In ThisWorkbook.Worksheets(Array _
>>          ("Sheet5", "Sheet6", "Sheet7", "Sheet8"))
>>         sht.Visible = xlVeryHidden
>>     Next sht
>> End Sub
>> 
>> I know I'm overloading you but use as much or as little as you want
>> 
>> If too confusing, send your workbook to my email.
>> 
>> gorddibbATshawDOTca
>> 
>> Replace the AT and DOT
>> 
>> 
>> Gord
>> 
>> On Sat, 20 Jun 2009 15:12:01 -0700, Michele
>> <Michele@discussions.microsoft.com> wrote:
>> 
>> >Dropdown was in E5 of "Index"...though I must not have done this correctly, 
>> >since it hid ALL of my worksheets, leaving only the last one...which btw was 
>> >very slick, even though not quite what I wanted :-)  Perhaps I got a tad 
>> >confused because you mentioned A1 but the code below references Range "H1"?
>> >
>> >There are worksheets that I need to remain visible (there are several that 
>> >are used in conjunction with the the ones I am trying to limit.  I need 
>> >Sheet1, Sheet2, Sheet3, Sheet4 to remain, but then hide Sheet5, Sheet6, 
>> >Sheet7 or Sheet8 depending on the dropdown.
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> Assuming your dropdown list is in A1 of "Index" sheet and the DV dropdown
>> >> contains a list of other worksheets.
>> >> 
>> >> Copy/paste these two events into the "Index" sheet module.
>> >> 
>> >> Private Sub Worksheet_Activate()
>> >> Dim sht As Worksheet
>> >> For Each sht In ActiveWorkbook.Sheets
>> >>         If sht.Name <> "Index" Then
>> >>             sht.Visible = xlVeryHidden
>> >>         End If
>> >>     Next sht
>> >> End Sub
>> >> 
>> >> Private Sub Worksheet_Change(ByVal Target As Range)
>> >>  If Intersect(Target, Me.Range("H1")) Is Nothing Then Exit Sub
>> >>     On Error GoTo endit
>> >>     Application.EnableEvents = False
>> >>     With Sheets(Target.Value)
>> >>         .Visible = True
>> >>         .Activate
>> >>     End With
>> >> endit:
>> >>     Application.EnableEvents = True
>> >> End Sub
>> >> 
>> >> Right-click on the tab and "View Code" to access the module.
>> >> 
>> >> Edit to suit........"A1" or "Index" may not be correct for you.
>> >> 
>> >> Alt + q to return to the Excel window.
>> >> 
>> >> Select a name from the dropdown list to hide all sheets except "Index" and
>> >> selected sheet.
>> >> 
>> >> To bring up another sheet, switch back to "Index"
>> >> 
>> >> 
>> >> Gord
>> >> 
>> >> 
>> >> 
>> >> 
>> >> 
>> >> On Sat, 20 Jun 2009 08:32:01 -0700, Michele
>> >> <Michele@discussions.microsoft.com> wrote:
>> >> 
>> >> >Yes!!  
>> >> >
>> >> >That's it exactly...I would like only the selected sheet unhidden, based on 
>> >> >the dropdown...stuff the rest in the closet :-)
>> >> >
>> >> >This is a workbook used by about 8 team members, all within my department.  
>> >> >
>> >> >We have been working from 12 different workbooks (each contained 4 
>> >> >worksheets that were identical, and the ABC123 xxx that was different) just 
>> >> >due to the differences.  By using formulas based off user input in specific 
>> >> >cells, I have been able to combine all 12 workbooks into 1 workbook with the 
>> >> >4 identical tabs and then the 4 different ABC123 xxx tabs.
>> >> >
>> >> >There are currently quality issues with work being completed in the 
>> >> >incorrect workbook. 
>> >> >
>> >> >The dropdown currently contains 4 selections, with the potential to expand 
>> >> >to 6 if I incorporate for another 2 person team.
>> >> >
>> >> >As far as the users being trusted to enable the macros, part of the training 
>> >> >on using the new workbook will be to stress the importance of doing so.  They 
>> >> >have been asking for something like this, so I do not think it will be an 
>> >> >issue.  It's a conscious decision on their part, and they will be accountable 
>> >> >if the QA team were to see all tabs.  
>> >> >
>> >> >This is why I posted in new users...I have some good working knowledge of 
>> >> >formulas, but am basically a VBA virgin (that is I have recorded some macros 
>> >> >and have been able to edit as needed).
>> >> >
>> >> >Thanks~
>> >> >Michele
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> Sounds like you want sheets hidden with only selected sheet from the list
>> >> >> becoming unhidden or is there another purpose behind limiting the access to
>> >> >> sheets only from the hyperlinks?
>> >> >> 
>> >> >> This would require VBA and some protection, which BTW is not that secure in
>> >> >> Excel.  How secure do you want this to be?
>> >> >> 
>> >> >> You could do away with the hyperlinks and just work directly off the
>> >> >> dropdown selection.
>> >> >> 
>> >> >> How many sheets do you have listed in your dropdown?
>> >> >> 
>> >> >> Would users be trusted to enable macros?
>> >> >> 
>> >> >> 
>> >> >> Gord
>> >> >> 
>> >> >> On Fri, 19 Jun 2009 14:36:01 -0700, Michele
>> >> >> <Michele@discussions.microsoft.com> wrote:
>> >> >> 
>> >> >> >Hi Gord,
>> >> >> >
>> >> >> >The user selects a tab option from a list.
>> >> >> >Examples would be:
>> >> >> >
>> >> >> >AB123 Jones family
>> >> >> >AB123 Smith family
>> >> >> >AB123 Anderson family
>> >> >> >
>> >> >> >In each of the 3 cells directly below the dropdown, I have placed hyperlinks 
>> >> >> >to each of the 3 tabs, that are formatted to 'appear' based on the selection 
>> >> >> >from the list.
>> >> >> >
>> >> >> >Ideally the user should use the hyperlink to access the correct tab in the 
>> >> >> >workbook,but in real life...well we know what happens...they click the tab 
>> >> >> >itself.
>> >> >> >
>> >> >> >Should they select AB123 Jones family, I do not want them to be able to 
>> >> >> >utilize the AB123 Smith/AB123 Anderson tabs via direct access.
>> >> >> >
>> >> >> >I know I could conditionally format each worksheet to 'gray out' based on 
>> >> >> >the initial selection, but am working within each with quite a bit of 
>> >> >> >conditional formatting already, so I am trying to find another 'safety', such 
>> >> >> >as a locked tab or a msgbox to re-direct.
>> >> >> >
>> >> >> >I am not very savvy with writing my own macro code, but am trying to learn.
>> >> >> >
>> >> >> >Thanks!
>> >> >> >
>> >> >> >
>> >> >> >"Gord Dibben" wrote:
>> >> >> >
>> >> >> >> What is conditionally formatted hyperlink?
>> >> >> >> 
>> >> >> >> How will Excel know if an incorrect selection has been made from the
>> >> >> >> dropdown?
>> >> >> >> 
>> >> >> >> 
>> >> >> >> Gord Dibben  MS Excel MVP
>> >> >> >> 
>> >> >> >> On Thu, 18 Jun 2009 21:04:01 -0700, Michele
>> >> >> >> <Michele@discussions.microsoft.com> wrote:
>> >> >> >> 
>> >> >> >> >Hi,
>> >> >> >> >
>> >> >> >> >I am creating a workbook, where I have several worksheet that are labeled 
>> >> >> >> >with the same first 7 characters "ab123 -".
>> >> >> >> >
>> >> >> >> >The user selects one of these sheets from a dropdown in the first worksheet.
>> >> >> >> >I provide a conditionally formatted hyperlink within worksheet A, based on 
>> >> >> >> >their selection, but for quality control want an additional step that either 
>> >> >> >> >locks an incorrect selection or presenting a msgbox to re-direct.
>> >> >> >> >
>> >> >> >> >Can this be done?
>> >> >> >> >
>> >> >> >> >Thanks
>> >> >> >> >Michele
>> >> >> >> 
>> >> >> >> 
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>> 

0
Gord
6/21/2009 9:31:09 PM
Reply:

Similar Artilces:

Lock Pivot Table Column Widths
Hi, Everytime I refresh my pivot table the column width changes. Is there a way to set and lock the column widths of the pivot table. Thanks, Christine Turn off AutoFormat in the PivotTable, and the column width should be retained: Right-click on a cell in the PivotTable Choose Table Options Remove the check mark from AutoFormat Table Click OK Christine wrote: > Hi, > > Everytime I refresh my pivot table the column width > changes. Is there a way to set and lock the column widths > of the pivot table. > > Thanks, > Christine -- Debra Dalgleish Excel F...

multiple worksheet formula help
I have a 7 page workbook. Page 1 is the "master" sheet containing formulated data from the other 6 sheets in the workbook. Each of the 6 other sheets are set-up with the exact same rows and columns - they are simply assigned to a different sales representative. I have the representatives "complete" a ROW on their sheet and "copy/paste" that row to the "master" sheet which then formulates the data via "sumproduct" formulas into charts. My problem is that I have 6 representatives who can't even copy/paste without making errors! Wha...

Sending copy worksheet to mailrecipient
Hey gyus :) I beleive there is a way to make a macro that will send a copy of my worksheet to a dedicated mailrecipient indicated in a cell in the worksheet, but I cant figure it out. Are there any compassonate Excel-guru who can lead me to this devine level of knowlegde? Most kindly regards Snoopy Check your other post. Snoopy wrote: > > Hey gyus :) > I beleive there is a way to make a macro that will send a copy of my > worksheet to a dedicated mailrecipient indicated in a cell in the > worksheet, but I cant figure it out. > Are there any compassonate Excel-guru who can...

Protecting Xcel worksheets
Hi All, I want to protect and hide formulas for a whole array of cells in spreadsheet ... Is there a way of doing this but retaining the ability to apply the Autofilter (under 'Data' menu option)? Many thax, Don- First you must have autofilter enabled then when protecting the sheet, allow "use autofilter" in options. Gord Dibben MS Excel MVP On Thu, 1 Apr 2010 06:13:01 -0700, Don <Don@discussions.microsoft.com> wrote: >Hi All, > >I want to protect and hide formulas for a whole array of cells in >spreadsheet ... Is there a way...

Import/Export XML in protected worksheet
I developped an excel tool on which most of the cells are protected (and I need to keep them protected). I mapped an XML schema on all the worksheet entries. I would like to be able to import and export all these entries but Excel does not let me do that when the worksheet is protected. Can anybody help? ...

New Appointment Window Locked up
Everytime I open a New Appointment window either by clicking the "New" button, or double clicking on a date, the window opens,, but is frozen for one to two minutes. Then it will allow me to enter a new appointment and all related info. Next new appointment, the same thing. What's going on? Windows Vista Home Premium; Office 2007, 4GB Ram ...

locking up
This might be more of a GDI question, but since I'm in MFC, I'll ask: Would BitBlt-ing to the screen repeatedly tend to lock up a program, after alt+tabbing to another application? If so, why? This seems to be happening to me, and I want to get to the bottom of the problem. What I have is either a fade loop or a screen-size redraw, both based on a timer and bitblt. Quite often, after I leave the program to go to another app, I cannot return and must "end task". "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:%23YFL5Ks%23F...

Locking a wroksheet
howaya folks, How does one lock a worksheet, so that others may not edit it. answers on a postcard.... -- johner ----------------------------------------------------------------------- johnerr's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2580 View this thread: http://www.excelforum.com/showthread.php?threadid=39422 Hi Johnerr Try:- Tools > Protection > Protect Sheet, you can the enter a password This will protect all cells in the worksheet If you want access to some cells for editing, before Protecting th sheet you will need to do the following:...

Can only put a single digit/letter in worksheet 2007
Hello, please can anyone advise. Just trying out Excel 2007. However when I try to enter data I can only put a single letter OR a single number in a cell! Also I cannot delete same. I have to close workbook. I am running windows XP and was running excel 2003 with no problems. Any advice would be gratefully recieved! Mike T -- Igor Are you still running the Beta version? This has now "died" and it is trying to tell you "I can do no more!!!" best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Igor Quasimodo" <IgorQuasimo...

Selecting Letters
Hi There is two parts to me questionPart 1 I need to populate a field with certain letters obtained from a clients surname and first name. Letters required are the 2nd, 3rd and 5th letter of the surname and then the 2nd, 3rd letter of their first name. And if there is no letter the a number "2" needs to be used instead.examples are as follows:Name Field RequiredJo Smith miho2Andrew Christopher hrsndJohn Rex ex2ohPart 2As there are already clients in the database i need to update all the client...

Period end payroll reports/posting liabilities
When trying to post payroll liabilities from the Period End Payroll Reports window, we get the following error for several users: A get/change operation on table "UPR_Distribution_WORK" failed. A record was already locked. We've already tried deleting the following tables: ACTIVITY, UPR10400, SY00800, SY00801, Dex_Lock and Dex_Session. Any ideas? ...

selected row count of list box
A2k Is there a way to get the selected row count of a list box dynamically as the user selects rows? Delphi has an event called "OnSelectionChanged" but Access is much more limited. I don't want the user to have to exit the list box or click a button or anything manual in order to see the # of rows he's selected. How can this be done? I know about "lstCusts.ItemsSelected.Count" but not sure what event to use it in to accomplish what I need. Thanks, Keith Never mind. I did this and it handles both mouse and keyboard selections: Private Sub lstCusts_AfterUpdat...

How to auto populate fields based on primary contact.
Hi, I hope someone can help, i an new to MS CRM and am trying to workout how to auto populate the primary contact job title, phone no and email based on the primary contact, and to update them is the primary contact changes. I have looked on the web and it appears i should be able to use a workflow, OnChange event or field mappings to achieve this. Would someone be able to tell me which would be the best option and some idea of how to do it. Thanks Richy81 You need custom code to do it, either a callout (3.0)/plug-in (4.0) or an OnChange event handler calling the web services. Some...

Lock Conditional Formatting
Good afternoon, Is there a way that I may lock the conditional formatting of a cell without locking out the ability to enter data into the cell? I have several columns of data, each column with a different conditional formatting scheme. I want to make sure that no one accidentally copies a cell in one row into another row or does "fill right/left" - which kills the formatting. When I protect the worksheet, which disables a user from having access to the formating buttons (text color, pattern, border), a user can still copy or fill the format of one cell to another. Any suggestio...

limit number of rows & colums in a worksheet
is there a way to limit the number of available colums & rows in a particular worksheet. --- Message posted from http://www.ExcelForum.com/ David, You can hide all the unused rows and columns, or you could set the ScrollArea property to a specific range address. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "davidbrowne17" <davidbrowne17.ya2hm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya2hm@excelforum-nospam.com... > is there a way to limit the number of available colums & rows in a > ...

Open on specific worksheet
How can I make an excell file alwasy open at a specific worksheet, no mater which sheet was active when saved Private Sub Workbook_Open() Worksheets("Sheet2").Activate End Sub -- Ian -- "Adrian" <Adrian@discussions.microsoft.com> wrote in message news:D85EF127-C2AC-471B-A2EA-D78FDE45FDDF@microsoft.com... > How can I make an excell file alwasy open at a specific worksheet, no > mater > which sheet was active when saved ...

Lock Message, Error 3218
I have a database that is split into back end and about 25 front ends. We updated from Access 97 to 2003. I now keep getting the following message: Could not Update, Currently Locked, Error 3218. It has been causing pure chaos. Can someone please provide any suggestions. I have compacted, repaired and re-linked tables via Linked Table Manager. It helps for a little bit, however will lock up again eventually. Any suggestions would be greatly appreciated. ...

Merging Worksheets
i have data coming in from various sources in worksheets which i have t merge into one main workbook but if i change the original worksheets th main sheet must update. copy and paste takes too long even if i lin it.. thought i could use an hlookup function to lookup the data bu cause the source is from various workbooks that doesnt work, so i hav landed up using the merge workbook function. problem there is if change an original it doesnt automatically update the main on -- Message posted from http://www.ExcelForum.com ...

formatting based on text
I need to format several docs every day. They're programs written in Notepad and any line that starts off with <! is a comment. My job is to copy the text from Notepad and change all comment lines to be white text with a black background. Is there some way for Excel to do this automatically? I know there are other programs besides Notepad but this is what I'm stuck with doing so I can't change the process. Sandra, You said you are copying it from notepad, is the final result going to be in excel or not? If it is you can use excel autoformating. "Sandra" wrote: ...

Worksheet not created
hello, I am getting 'no worksheet created' I am getting this while trying to create a global customer -any suggestions This is a multi-part message in MIME format. ------=_NextPart_000_00AB_01CB3AF4.AD390EA0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Trinidave, Make sure that the machines time and time zone is correct. Both the = server and the machine that created the customer. Jeff Check Point Software Search this newsgroup -- http://tinyurl.com/2lmk4w (Stolen from DRS) =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D...

How to copy ActiveX Controls which are locked on a sheet
I have a couple buttons and a CheckBox on a row; all are ActiveX Controls. I’m trying to insert a row (lots of other things going on below) and then copy/paste the row above into the current (ActiveCell) row. I want to bring in the RadioButtons and the CheckBoxes down one row. It seems to work when I am in design mode, but when I get out of design mode, the controls are locked. Actually, this is what I want (I don’t want users moving controls around on the sheet). I don’t know if I can programmatically get into Design Mode, that’s the problem. Or, I’d be happy to use Form C...

Where am I being locked out?!
This is an unusual problem and I'm hoping someone will have come across something similar before. Before I start I should state that I've hardly used Windows Server or Active Directory, so go easy on me if this is something obvious! I am having my account locked out on a daily basis. I know that this isn't to do with my machine as it happens even if I get my account unlocked and wait a day without turning my machine on I still can't log on the day after. I'm pretty sure it's a piece of software that I've installed somewhere that keeps trying to con...

Need formula based on criteria.
I have a spreadsheet listed by day as follows. I need the data from column B, C & D summarized by month. How do I do that? Col A Col B Col C Col D 01/01/04 5 5 4 02/01/04 6 6 6 03/03/04 7 0 0 I need the result to look like this - Jan 04 11 11 10 Mar 04 7 0 0 How do I tell it to search for dates between 01/01/04 - 31/01/04 in column A then add column B, C or D to comue up with my totals for Jan 04? Hi one way for Jan Col B use the following formula =SUMPRODUCT(--(MONTH($A$2:$A$4)=1),B$2:B$4) this can t...

Macro command to move cell selection
I am writing a macro for a spreadsheet where I have values stored in one column. I want to run a loop where each time the loop runs, I select each cell in the column. For example, my values are stored in column A, cells 1 through 7. On loop 1 I want to select cell A1, loop 2 select cell A2, loop 3 select A3 and so on. How can I set this up in a macro to increment cell selection as I move through my loop? I'm really not sure whay you can do with this, but is this what you are wanting? Sub SelectionLoop() Dim i As Long For i = 1 To 7 Cells(i, "A"...

Create Named List (range) based on check list
Column A is a list. Column B has =93x=94 in some cells. I need to create a named range, using a formula, that is populated with items from Column A when there is a corresponding =93x=94 in column B. I cannot use a macro in this workbook. I=92ll be grateful for any help Robert Robert Hatcher submitted this idea : > Column A is a list. Column B has “x” in some cells. I need to create a > named range, using a formula, that is populated with items from Column > A when there is a corresponding “x” in column B. > I cannot use a macro in this workbook. > I’ll be grateful for any help ...