Re: Query on Control Source in Userform Textbox

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Jake Marx (msnews_at_longhead.com)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 07:34:33 -0700

Hi Golf,

Excel gives you a way of protecting a work***'s contents from direct user
input while leaving it open to programmatic change. Just set the
UserInterfaceOnly argument of the Protect method to True:

    Sheets("Sheet1").Protect Password:="****", UserInterfaceOnly:=True

This will allow your UserForm to modify the cells (via the ControlSource
property) but will stop users from changing the values directly.

This statement only allows programmatic changes during the current session
of Excel. Once you close and reopen the workbook, it will be
fully-protected. So in order to have this work all the time, you'll have to
use that statement each time the workbook is opened. You can put that line
of code in the Workbook_Open event routine. Since you have to put the
password in your code, you should also protect your VBA Project with a
password (via Tools | VBAProject Properties in the VBE). 

-- 
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address unmonitored]
golf4 wrote:
> Hi, Jake -
> Thanks so much for your response. Your code suggestion works like a
> charm.
>
> I was hoping, as long as I had you on the line, to ask a follow-up
> related question. When I originally designed our Income Calculation
> Tool, users would enter data directly into fields in the Data Entry
> ***. Because the Data Entry *** contains 100s of formulas, command
> buttons, etc., I have both the specific cells and the *** password
> protected. Since I now have users using userform textboxes to enter
> data into the Data Entry ***, I'm finding that when I protect the
> cells that are tied together with the specific textbox (Control
> Source), I receive the error message "Exception Occured". I'm
> wondering whether there is a way to resolve this while still
> protecting the data entry method via userform textboxes?
>
> Thanks again for the help,
>
> Golf
>
>
> "Jake Marx" <msnews@longhead.com> wrote in message
> news:<u3$YNSO9DHA.2576@TK2MSFTNGP09.phx.gbl>...
>> Hi Golf,
>>
>>> [[[If ("Data_Entry_***!B75") >"" Then
>>> Sheet8.PrintOut]]]
>>
>> You forgot to tell VBA that you wanted to deal with a Range.  So
>> your code is comparing the string "Data_Entry_***!B75" with an
>> empty string (""), and the previous will always be greater than the
>> latter.
>>
>> Instead, you could use:
>>
>>     If Len(Sheets("Data_Entry_Sheet").Range("B75").Value) Then
>>
>> This will force a printout only if cell B75 on Data_Entry_*** has
>> a value.
>>
>> --
>> Regards,
>>
>> Jake Marx
>> MS MVP - Excel
>> www.longhead.com
>>
>> [please keep replies in the newsgroup - email address unmonitored]
>>
>>
>> golf4 wrote:
>>> Hi, everybody -
>>>
>>> BOY O' BOY --- I continue to improve our agency's Income Calculation
>>> Tool with the help of numerous people here. I have a slight problem
>>> in one of my VB codings. My particular problem is related to the
>>> portion of the code that is seperated and in brackets. I have
>>> created a specific userform that includes a number of textboxes
>>> where the user enter data that feeds over to the spread***'s Data
>>> Entry ***. The Data Entry *** then calculates the family's
>>> income and rent whish then feeds over to all of the applicable
>>> program letters and worksheets.
>>>
>>> Within the userform, I have a data entry textbox with the Control
>>> Source set to enter the data entered into the textbox to feed into
>>> the field Data_Entry_***!B75. What's supposed to happen is: if
>>> Data_Entry_***!b75 is populated, via the userform's textbox,
>>> Work*** 8 should be printed out - if the field is empty,
>>> Work*** 8 should not be printed. WHAT IS HAPPENING --- everytime
>>> I run the code, even when Data_Entry_***!b75 is empty, the
>>> work*** prints out!!!
>>>
>>> Sorry for the extended query. I'm hoping one of the experts here can
>>> give me a clue why this is happening and how I can fix the code.
>>>
>>> CODE BELOW:
>>>
>>  <snip>
>>>
>>> [[[If ("Data_Entry_***!B75") >"" Then
>>> Sheet8.PrintOut]]]
>>>
>> <snip>
Quantcast