Lock out a portion of spreadsheet based on a condition
 

I am new to VBA programming in Excel and I have a couple questions about tabbing through a spreadsheet. My spreadsheet has a top portion where users will input their data and then based on calculations of that data they will get an output on the bottom of the page. I have created a Macro that is assigned to a Button that Resets the information that the users put in. Included in this Macro is a way to protect the worksheet so that the users can only change the cells where their input is necessary. This protection also enables me to set a tab order for how the users navigate the input portion of the worksheet

Now I am having a couple of issues with the tab order. First, in one of the input boxes the users choose between two options (A and B). If they choose option "A" then there is an input section on the spreadsheet that they do not need to fill in (Cells C15:C20,K15:K19). What I would like to accomplish is to have the tab order skip this input section if "A" is selected, but to not skip this input section if "B" is selected

The other issue that I am having has to do with drop down (or combo boxes). I have several of these as input areas for the users. Currently, when the user tabs through the input section of the worksheet they tab to the cell behind the combo box. For instance the tab order places the cursor in cell C9. This is the cell that the combo box is associated with, but the cursor is not in the combo box. So to get the selection that the user wants they either need to go to the mouse, or know exactly what the choice is they want to choose without seeing the options. What I would like to do is be able to tab into the combo box and have it immediately drop down so that the users can see what the selection choices are, then have it so that they can just type the first couple of letters and the choice will be pulled into the combo box, and finally be able to hit the tab key to move onto the next box in my tab order

Any help with this problem will be greatly appreciated

Thanks in advance
Ryan

Lock out a portion of spreadsheet based on a condition
 

when a response is made that would require by-passing the specified range,
have your code unprotect the sheet, lock the cells (C15:C30, K15:K19), then
protect the sheet.

--
Regards,
Tom Ogilvy

"Ryan" wrote in message
...
I am new to VBA programming in Excel and I have a couple questions about
tabbing through a spreadsheet. My spreadsheet has a top portion where users
will input their data and then based on calculations of that data they will
get an output on the bottom of the page. I have created a Macro that is
assigned to a Button that Resets the information that the users put in.
Included in this Macro is a way to protect the worksheet so that the users
can only change the cells where their input is necessary. This protection
also enables me to set a tab order for how the users navigate the input
portion of the worksheet.

Now I am having a couple of issues with the tab order. First, in one of
the input boxes the users choose between two options (A and B). If they
choose option "A" then there is an input section on the spreadsheet that
they do not need to fill in (Cells C15:C20,K15:K19). What I would like to
accomplish is to have the tab order skip this input section if "A" is
selected, but to not skip this input section if "B" is selected.

The other issue that I am having has to do with drop down (or combo
boxes). I have several of these as input areas for the users. Currently,
when the user tabs through the input section of the worksheet they tab to
the cell behind the combo box. For instance the tab order places the cursor
in cell C9. This is the cell that the combo box is associated with, but the
cursor is not in the combo box. So to get the selection that the user wants
they either need to go to the mouse, or know exactly what the choice is they
want to choose without seeing the options. What I would like to do is be
able to tab into the combo box and have it immediately drop down so that the
users can see what the selection choices are, then have it so that they can
just type the first couple of letters and the choice will be pulled into the
combo box, and finally be able to hit the tab key to move onto the next box
in my tab order.

Any help with this problem will be greatly appreciated.

Thanks in advance,
Ryan

Lock out a portion of spreadsheet based on a condition
 

Have u considered using a VBA userform for your data entry, that way you can control the tab order of all ur controls, and direct the data to the proper cells, then calculate.

Lock out a portion of spreadsheet based on a condition
 

Just as you might suspect:

Activesheet.unprotect password:="ABCD"
ActiveSheet.range("C15:C20,K15:K19").Locked = True
ActiveSheet.Protect password:="ABCD"


--
Regards,
Tom Ogilvy


"Ryan" wrote in message
...
Tom,

This sounds like exactly what I am looking for, but I am unsure how to
code it (I am still getting my feet wet). If you could provide me an
example I would GREATLY appreciate it.

Thanks,
Ryan

Lock out a portion of spreadsheet based on a condition
 

Tom

Don't I need an If...Then code piece too? I want those cells locked only if "A" is chosen in the combo box that is tied to cell C9. I can't seem to get my code right for this as I have tried several times with no success

You have been of great help so far, and I appreciate you taking your time to help me

Ryan

Lock out a portion of spreadsheet based on a condition
 

Of course you would, but it was assumed you knew how to write an if
statement.

If Range("C9").Value = "A" then


End If

Of course it is a mystery where you will put this code or what other code
might be necessary to prevent it being executed when not needed.

--
Regards,
Tom Ogilvy


"Ryan" wrote in message
...
Tom,

Don't I need an If...Then code piece too? I want those cells locked only
if "A" is chosen in the combo box that is tied to cell C9. I can't seem to
get my code right for this as I have tried several times with no success.

You have been of great help so far, and I appreciate you taking your time
to help me.

Ryan

Lock out a portion of spreadsheet based on a condition
 

Tom

It still is not working. Here is a copy of the code as I have written it

Sub Test(


Dim ws As Workshee

Set ws = ActiveShee
'Locking the Current Loan information section if Purchas

If Range("C9").Value = "A" The
'Skip the Current Loan Information Sectio
ActiveSheet.Unprotec

ActiveSheet.Range("C15:C20,K15:K19").Locked = Tru

ActiveSheet.Protec

End I


End Su

When I try and run it, I get the error message; "Unable to set the locked property of the Range class".

I really am VERY new to this and have been trying to teach myself, but it is coming slowly. I am sure that I am making a simple mistake that you will be able to fix.

Also, I want it set up so that it knows to run the second that "A" is selected in the combobox associated with C9

Thank you very much for all of your help

Rya

Lock out a portion of spreadsheet based on a condition
 

It worked fine for me.

Tested in xl97.

If you are using xl97 and calling this from a command button then change
the TakefocusOnClick property to false.
or put in ActiveCell.Activate as the first command in the sub if using xl97
and calling from another activeX control from the control toolbox toolbar.

--
Regards,
Tom Ogilvy

"Ryan" wrote in message
...
Tom,

It still is not working. Here is a copy of the code as I have written it:

Sub Test()


Dim ws As Worksheet

Set ws = ActiveSheet
'Locking the Current Loan information section if Purchase

If Range("C9").Value = "A" Then
'Skip the Current Loan Information Section
ActiveSheet.Unprotect

ActiveSheet.Range("C15:C20,K15:K19").Locked = True

ActiveSheet.Protect

End If


End Sub

When I try and run it, I get the error message; "Unable to set the locked
property of the Range class".

I really am VERY new to this and have been trying to teach myself, but it
is coming slowly. I am sure that I am making a simple mistake that you
will be able to fix.

Also, I want it set up so that it knows to run the second that "A" is
selected in the combobox associated with C9.

Thank you very much for all of your help!

Ryan

Lock out a portion of spreadsheet based on a condition
 

also, the code should be in a general module (insert=Module), not in a
worksheet module, the Thisworkbook module or a userform module.

--
Regards,
Tom Ogilvy


"Ryan" wrote in message
...
Tom,

It still is not working. Here is a copy of the code as I have written it:

Sub Test()


Dim ws As Worksheet

Set ws = ActiveSheet
'Locking the Current Loan information section if Purchase

If Range("C9").Value = "A" Then
'Skip the Current Loan Information Section
ActiveSheet.Unprotect

ActiveSheet.Range("C15:C20,K15:K19").Locked = True

ActiveSheet.Protect

End If


End Sub

When I try and run it, I get the error message; "Unable to set the locked
property of the Range class".

I really am VERY new to this and have been trying to teach myself, but it
is coming slowly. I am sure that I am making a simple mistake that you
will be able to fix.

Also, I want it set up so that it knows to run the second that "A" is
selected in the combobox associated with C9.

Thank you very much for all of your help!

Ryan

Lock out a portion of spreadsheet based on a condition
 

Tom

I took the code to another worksheet and was able to get it to work...and also realized why it isn't working in the original spreadsheet. I don't want the user to have to run the macro. Most of the users of this spreadsheet will know very little about computers (Which is why I am trying to make their data input as simple as possible)

Is there a way to have the macro run automatically when "A" is selected? I am trying to make it like an If formula in excel so that way the range of cells is locked anytime that "A" is in Cell C9, but that they are unlocked anytime "A" is not selected in cell C9

Thanks for your hel

Ryan

Lock out a portion of spreadsheet based on a condition
 

Tom

I am still getting the same error message (Unable to set the locked property of the range class) when I try and run the macro in my original spreadsheet. I cannot figure out what is wrong! Especially since the same code seems to work in another spreadsheet. The error appears to be with the range...locked=true portion of the code

Please help me

Ryan

Lock out a portion of spreadsheet based on a condition
 

Tom, Are you still here with me???