Adding Worksheet Protection But Allowing Form Controls - Macros
Jun 5, 2014
I have a worksheet with lots of things going on... the end user needs access to the Name field, DOB field, all the scroll and form control option buttons in the 9-question sheet, and the macro-enabled reset button at the bottom. Formulas are sprinkled throughout the worksheet, in columns T through AC... option button links go to AA and AB.
I would like to protect the entire sheet so none of the fields, except where indicated above, could be selected or edited... but when I protect the sheet, I get errors when trying to use you form controls, and the formulas do not respond. And finally, when protected, I get a debug error on the reset macro.
I need to make several exact duplicates of an existing worksheet which includes several form controls. Basic Copy/ Paste the worksheet works well but row heights are different and hidden rows are unhidden. Is there a way to make an EXACT duplicate ?
I'm adding a form to a worksheet for the first. I tried to follow an example from the internet and then adapt for own form but have got lost and don't know where.
Attached is the excel file i am working on. On the summary worksheet i have added a button which works fine and opens up the form i have made.
My problem is it doesn't enter the data into the relevant cells on the relevant worksheets. I think the form should be quite self explanatory.
I have a workbook that I would like to share and allow multiple users to add and make changes at the same time. However, the workbook is full of macros that really need to run. Is this possible? The workbook is written in excel 2003.
If this is not possible then is it possible to share the workbook in a similar fashion but not allow any other users to make changes, but update their workbook with the changes that I make?
i want to do the following: initialize a sheet by showing form1, which accepts user input for rows and cols name a range on the sheet of size rows x cols display form2 which is just a "DONE" button that they click when they're finished pause/stop the macros and allow the user to fill in values for that range start up some other macros when they press the "DONE" button.
i tried it and found that when you display a form it locks the sheet (can't select the sheet to input values) and you can't even alt tab away from the form. is there a way around this?
I have a scoresheet with 60 contestants. Each contestant takes up 7 rows, the first six of which are hidden to start with and I have put macros in the adjoining column so that when they are clicked, the full 7 rows open up and the table of scores can be entered, When entry is complete for that contestant, a further macro when clicked will close up the 6 rows, leaving just the main line (line 14) with the No, Name, “OPEN” macro and other Totals in adjoining columns.
The sheet works fine, but as many people will use this programme, I need to protect the sheets against mistaken entries etc., and as soon as I protect it, the macros wont work and throw up a “unable to set the property of a hidden range class, run time error 1004. I don’t want to leave the sheet unprotected, can anyone advise me where I am going wrong.
I am also trying to find a way to validate “time taken” entries so that they can only be input as minutes and seconds in the format of 09.56, within a range of 00.01 – 10.00. Not having any success with this as it keeps converting the data into something like a date.
I have a excel Workbook, 6 sheets and many calculations with formulas and macros.
Is there a way to protect this workbook to be able to insert data only in the correct cells, I tried but the macros does not work, they are essentially copy and paste.
there will be 3 sheets with reports to be printed too.
In addition to the code below that hides data if macros not enabled, prevents copy and cut (I know its still possible) I would like to as well as protect the sheets (which works fine) also protect the workbook to prevent moves etc. Unfortunately while the protection may be easily applied, the veryhidden bit to ensure macros are selected then conks out. Does anyone out there know of a way where say the workbook protection may remain and at the same time allow the veryhidden etc bits do their stuff as normal as if the workbook protection was off?
Private Sub Workbook_BeforeSave _ (ByVal SaveAsUI As Boolean, Cancel As Boolean) If Cancel = True Or bIsClosing = False Then Run "HideAll" If SaveAsUI = True Then Cancel = True
End Sub Private Sub Workbook_Activate() Dim oCtrl As Office.CommandBarControl 'Disable all Cut menus For Each oCtrl In Application. CommandBars.FindControls(ID:=21) oCtrl.Enabled = False 'oCtrl.Enabled = True Next oCtrl...................
I would like to be able to password protect each sheet in a workbook and still be able to use the macros/form control (button). Below is the code that I'm using and the code that I've attempted to add to it to protect it.
Current Code:
[Code] ....
Code added for protection but not working:
[Code] ....
I've added this code to both the beginning and the end of my code; however, I'm getting a "400" error when I try to use the form control (button) when the sheet is protected. Is there something that I'm doing incorrectly?
I prtected several sheets with the xlVeryHidden command I got from you. But when the user does not allow Macros to be executed, also these protections are not executed. Does there exist any solution to this problem? Something like "when macros are not allowed do NOT open file"?
Here is a sample of code I am having problems with:
Sub CheckBox1_Click() Range("Make") = Range("Make1").Value Range("Model") = Range("Model1").Value Application.Run ("EnterPrice") Aplication.Run ("Unit Selection") CheckBox1.Value = True End Sub
I am having problems with the named ranges "Make" and "Model" which are on another sheet and with runninh the "EnterPrice" and "Unit Selection" Macros which are in Module1, I get the error message "Method 'Range' of object '_Worksheet' class failed"
I have a problem where a client is asking to have an Excel Spreadsheet completely secured. In itself this is a relatively painless exercise, but the problem lies in the fact that the workbooks have a lot of cross-sheet navigation by way of internal Hyperlinks.
My first thought was to unlock the cells that contain the the Hyperlinks and then allow selecting unlocked cells in the sheet protection properties, but this has an adverse affect in that it means clicking anywhere on the page will execute the hyperlink as if you had clicked on it directly. This isn't going to be sufficient in this case.
The Hyperlinks are simply added to the cell itself, but there are a lot of them and this template may be processed and updated up to 1000 times a month, so I need something that can handle being automated. My only other thought is to hand the document over to Acrobat and PDF it to allow security, but automating PDF creation is testy at the best of times.
I making a data entry form that sereval employees will be using. But what I need is data quality protection. My idea is to have a userform with a combo box. The employees would select there name from the combo box and then enter there personal password to get access to the data entry information, which will appear if the password is correct but kept hidden if password is incorrect. I have a example.xls without any code contain in it.
I have two linked ComboBoxes on a form. There are 10 Textboxes populated by ComboBox2s choice. These controls are situated on two frame controls which are used for visual groupings only. I would like to clear everything except Combo1 when Combo1s value is changed. ( Combo1 is a filtered key list using the dictionary. Script code)
Ive written a small sub that is called to clear the textboxes, but its not reliable every time. It seems that if the scroll bar is used, and the user chooses Combo1 choice NUTS/SEEDS, the boxes arent cleared. In fact it will show the Walnuts info in Combo2.
Is there a short way to clear text boxes, check boxes and option buttons in one command. I dont want to delete them I want them to be able to accept more data once the operator has finished
I have form controls on my worksheets. How do I hide them? When I hide the row, the controls stay there. These controls don't seem to have that visible property like the VB control.
I'm thinking of using a validation->list instead, but then you don't see the drop down arrow.
I have 30 textboxes on my form, named "TextBox1" to "TextBox30". When the user clicks the button to launch the form, I want to first hide all the textboxes (already done).
Then I want to loop from 1 to some predetermined number (between 1 and 30), and update the textbox whose name contains the number of the loop. For e.g.:
Sub ShowForm Dim ctl
For Each ctl In frmKFF.Controls If Left(ctl.Name,7) = "TextBox" Then ctl.Visible = False End If Next ctl
When I put form controls onto a spread sheet is there anywhere I can easily see and access its properties? Format Control is lacking many values and I do not see them in the vba project toolbar. I am trying to get checkbox locations and checkbox numbers if possible. If there isn't an easy place to see these is it still possible to get the checkbox locations in VBA?
Imagine I have a form with a few controls on it. And the controls properties are set up at runtime.
Is there any way possible to save the property changes that were made at runtime to the controls themselves? I mean - short of manually editing all the controls at Design time?
This might be easier to explain by example. See dummy code below. Using this example - I want to find something that will save the Caption of CommandButton1 as "TestMe" (rather than have it only temporarily set at runtime).
I have a userform with a large number of textboxes. Say 100. I want to clear them all after the user submits the data to the spreadsheet. Essentially resetting the userform.
My form controls (cmd buttons) and images don't appear or don't work. I was working on a macro to cut and paste a set of cells (protected sheet, I did unprotect it), and I noticed the cmd button tied to this disappeared. It then re-appeared but I couldn't engage it. This is part of a larger workbook that consists of a single page of cmd buttons that release specific worksheets (no activex). These buttons disappeared and re-appeared and don't work either. When I select the button I can see the cell underneath light up. It's like the image is there but nothing else?
The macros run fine from VBA editor. I was thinking the issue had something to do protections? There are more than 5 passwords (set on a worksheet) being used here, there is a fair amount of protecting and unprotecting going on, and I loop through the worksheets several times. Everything was working fine and now I'm stuck. I was getting a the-object-invoked-has-disconnected-from-its-client error related to an "insert cells" command on a co-workers computer, but not on my machine. This is actually what I was trying to solve when it crapped out. I would delete the buttons and create them all again, but I can't even select them to delete them.
I am creating a form that performs several different functions. I would like to force a sequence of these functions by enabling/disabling the controls based on a variable.
For example, a form as two buttons Button1 and Button2. By default, Button1 is ENABLED and Button2 is DISABLED. When Button1 is clicked, a macro is run, where an Enable_Button2variable is initialized to FALSE. The last line of the macro sets the Enable_Button2 variable to TRUE. Then, because Enable_Button2 is now TRUE, Button2 then becomes ENABLED.
I am trying to apply this strategy to a ComboBox, ListBox and CommandButtons. I'm not sure what event to trigger on to poll the Enable_Button2 to enable/disable the control.
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
I am trying to dynamically add controls to my user form based on some values in my cell. I am successfully able to create a text box dynamically but my label is not getting displayed. here is my code
Private Sub UserForm_Activate()
On Error Resume Next If (ThisWorkbook. Sheets("Sheet2").Cells(1, 8) <> "FALSE") Then Dim ctl As Control Dim ctl1 As Control
Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True) With ctl1
Is it at all possible to create a User Form where the number of CheckBoxes will be linked to the number of entries in a cell range?
For example I have a 3 records in the range A:A called "Blue", "Green", "Yellow". I want to have a user form with 3 CheckBoxes with the same caption names.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
