Tjyylli

Dilemma of explaining to interviewer that he is the reason for declining second interview

Writing a character who is going through a civilizing process without overdoing it?

It took me a lot of time to make this, pls like. (YouTube Comments #1)

Table formatting top left corner caption

what does しにみえてる mean?

Can a hotel cancel a confirmed reservation?

Roman Numerals equation 1

Using only 1s, make 29 with the minimum number of digits

Word or phrase for showing great skill at something WITHOUT formal training in it

Which one of these password policies is more secure?

One Half of Ten; A Riddle

How do you funnel food off a cutting board?

Why do neural networks need so many training examples to perform?

Why has the mole been redefined for 2019?

Can an insurance company drop you after receiving a bill and refusing to pay?

Is that a center tap tranformer just labelled differently?

Does SQL Server 2017, including older versions, support 8k disk sector sizes?

Intern applicant asking for compensation equivalent to that of permanent employee

Eww, those bytes are gross

What is 6÷2×(1+2) =?

Why are the books in the Game of Thrones citadel library shelved spine inwards?

How to deal with an incendiary email that was recalled

Can I string the D&D Starter Set campaign into another module, keeping the same characters?

Why zero tolerance on nudity in space?

How do I disable the row/column unhide option in a shared Excel file with vba?

Excel workbook protection: how can I stop users tampering with a workbook?Allowing users to unhide (format) only chosen columnsExcel 2007 Unprotect Sheet prompt in password protected sheets on Windows 7 machinesTick box with macroHow do I access *unsaved* Excel workbooks in VBA?Hide & Unhide macros won't work after locking columns & protecting sheetExcel : How to restrict maximum number of users to an shared excel file in our organisation's serverHow do dynamically hide cells/sheets without macrosVBA code to protect sheets and show/hide buttonsOpen specific file with VBA macro on macOS, Excel 2019

1

I have a shared Excel file that I need multiple people to work from at once.
The file contains some macros that hide certain columns based on the username.

• 1) check for username


• 2) hide columns A, B, C (for specific users)

Once the columns are hidden for a "restricted" user, I don't want the user to be able to unhide the columns by just right mouse clicking and selecting "unhide". I can't really protect/unprotect the sheet using the macro (-> since it's a shared file. it's throwing an error 1004, I believe). To protect the sheet via Excel toolbars won't work either (hiding / unhiding sheets in macros doesn't work on protected sheets).

To summarize:

Shared file -> unprotected sheet (prb: users can unhide hidden columns)

Shared file -> protected sheet (prb: can't hide columns using macro)

So I am thinking, I don't need the sheet protected. I just want to make sure that once the columns are hidden that the user won't be able to unhide them. Any ideas how to do that?

Perhaps, disable user control or something. I am not very familiar with vba.

Any tips or suggestions would be appreciated.

Thank you!

macros vba

share|improve this question

asked May 16 '12 at 22:48

VasekVasek

84

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  if you can keep that data on other sheets, you can use .visible=xlVeryHidden to hide the sheet from being unhidden from the right click menu
  
  – SeanC
  Jun 8 '12 at 15:21
  

  
  
  
  

add a comment | 

1

I have a shared Excel file that I need multiple people to work from at once.
The file contains some macros that hide certain columns based on the username.

• 1) check for username


• 2) hide columns A, B, C (for specific users)

Once the columns are hidden for a "restricted" user, I don't want the user to be able to unhide the columns by just right mouse clicking and selecting "unhide". I can't really protect/unprotect the sheet using the macro (-> since it's a shared file. it's throwing an error 1004, I believe). To protect the sheet via Excel toolbars won't work either (hiding / unhiding sheets in macros doesn't work on protected sheets).

To summarize:

Shared file -> unprotected sheet (prb: users can unhide hidden columns)

Shared file -> protected sheet (prb: can't hide columns using macro)

So I am thinking, I don't need the sheet protected. I just want to make sure that once the columns are hidden that the user won't be able to unhide them. Any ideas how to do that?

Perhaps, disable user control or something. I am not very familiar with vba.

Any tips or suggestions would be appreciated.

Thank you!

macros vba

share|improve this question

asked May 16 '12 at 22:48

VasekVasek

84

bumped to the homepage by Community♦ 10 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  if you can keep that data on other sheets, you can use .visible=xlVeryHidden to hide the sheet from being unhidden from the right click menu
  
  – SeanC
  Jun 8 '12 at 15:21
  

  
  
  
  

add a comment | 

I have a shared Excel file that I need multiple people to work from at once.
The file contains some macros that hide certain columns based on the username.

• 1) check for username


• 2) hide columns A, B, C (for specific users)

Once the columns are hidden for a "restricted" user, I don't want the user to be able to unhide the columns by just right mouse clicking and selecting "unhide". I can't really protect/unprotect the sheet using the macro (-> since it's a shared file. it's throwing an error 1004, I believe). To protect the sheet via Excel toolbars won't work either (hiding / unhiding sheets in macros doesn't work on protected sheets).

To summarize:

Shared file -> unprotected sheet (prb: users can unhide hidden columns)

Shared file -> protected sheet (prb: can't hide columns using macro)

So I am thinking, I don't need the sheet protected. I just want to make sure that once the columns are hidden that the user won't be able to unhide them. Any ideas how to do that?

Perhaps, disable user control or something. I am not very familiar with vba.

Any tips or suggestions would be appreciated.

Thank you!

macros vba

share|improve this question

asked May 16 '12 at 22:48

VasekVasek

84

share|improve this question

asked May 16 '12 at 22:48

VasekVasek

84

share|improve this question

asked May 16 '12 at 22:48

VasekVasek

84

asked May 16 '12 at 22:48

VasekVasek

84

asked May 16 '12 at 22:48

VasekVasek

84

1 Answer
1

active

oldest

votes

0

@Sean has mentioned putting the hidden data on a different sheet which was my first thought too.

My second thought was to have an auto-open macro that checks the current user and hides the columns then turns on protection. Not the most secure since the VBA code will need the password but it does get round the other issues. You can also protect the code which will reduce the issue of embedding the password.

In the past, when I had a complex workbook with parts that needed to be hidden, I had some macro's in my personal.xls that helped lock/unlock and hide/unhide the appropriate parts.

share|improve this answer

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f425466%2fhow-do-i-disable-the-row-column-unhide-option-in-a-shared-excel-file-with-vba%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

@Sean has mentioned putting the hidden data on a different sheet which was my first thought too.

My second thought was to have an auto-open macro that checks the current user and hides the columns then turns on protection. Not the most secure since the VBA code will need the password but it does get round the other issues. You can also protect the code which will reduce the issue of embedding the password.

In the past, when I had a complex workbook with parts that needed to be hidden, I had some macro's in my personal.xls that helped lock/unlock and hide/unhide the appropriate parts.

share|improve this answer

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

add a comment | 

0

@Sean has mentioned putting the hidden data on a different sheet which was my first thought too.

My second thought was to have an auto-open macro that checks the current user and hides the columns then turns on protection. Not the most secure since the VBA code will need the password but it does get round the other issues. You can also protect the code which will reduce the issue of embedding the password.

In the past, when I had a complex workbook with parts that needed to be hidden, I had some macro's in my personal.xls that helped lock/unlock and hide/unhide the appropriate parts.

share|improve this answer

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

add a comment | 

@Sean has mentioned putting the hidden data on a different sheet which was my first thought too.

My second thought was to have an auto-open macro that checks the current user and hides the columns then turns on protection. Not the most secure since the VBA code will need the password but it does get round the other issues. You can also protect the code which will reduce the issue of embedding the password.

In the past, when I had a complex workbook with parts that needed to be hidden, I had some macro's in my personal.xls that helped lock/unlock and hide/unhide the appropriate parts.

share|improve this answer

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

share|improve this answer

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535

answered Jun 29 '12 at 11:12

Julian KnightJulian Knight

12.9k11535