Enable Disable Form Controls Based On Criteria
Oct 20, 2007
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.
View 3 Replies
ADVERTISEMENT
Mar 5, 2008
I'm currently building an Excel database. This database has a few comboboxes and checkboxes that are tied to each other. If I select the combobox option of "Years", the "Quarters" combobox must be greyed out. In addition depending on which option is chosen, the relevant check boxes must be enabeled or disabled. I've written some code which runs fine, until I try and activate another macro button or if I add and rename a sheet(which is not linked to anything). What should I do? Here is a sample of my code. I have a feeling it's really obviouse but I cant see it..
Private Sub ComboBox2_Change()
If Sheets("InputSheet").ComboBox2 = "Years" Then
Sheets("InputSheet").ComboBox3.Value = ""
Sheets("InputSheet").ComboBox3.Enabled = False
Sheets("InputSheet").CheckBox6.Enabled = True
Sheets("InputSheet").CheckBox7.Enabled = True
Sheets("InputSheet").CheckBox8.Enabled = True
Sheets("InputSheet").CheckBox9.Enabled = True
Sheets("InputSheet").CheckBox10.Enabled = True
Sheets("InputSheet").CheckBox11.Enabled = True
Sheets("InputSheet").CheckBox12.Enabled = True
Sheets("InputSheet").CheckBox13.Enabled = True
Sheets("InputSheet").CheckBox14.Enabled = True
End If...................
View 3 Replies
View Related
Oct 23, 2008
I am attempting to modify a spreadsheet so that it will enable a range on Sheet3 (I9:K42) if a check box is checked on Sheet2. If it is not checked, it will lock that range on Sheet 3 and grey (or gray, if you prefer) out that range.
View 9 Replies
View Related
Jan 26, 2007
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies
View Related
Oct 27, 2006
I have an Excel userform that has 10 textboxes named, say, tbLabel1, tbLabel2, ... tbLabel10. They are all disabled in the beginning. Then I have a question for the user "how many labels you want" and another textbox for the answer. If the user entered, say, 6 as the answer, I want enable the first 6 textboxes, tbLabel1 - tbLabel 6. I tried using
For I=1 To tbHowMany.value
tbLabel & I.Enabled = True
Next I
View 3 Replies
View Related
Feb 22, 2010
i want to dis able and enable this button using three criteria, and auto paste the comment to other sheet.
View 2 Replies
View Related
Mar 2, 2014
Is it possible to enable/disable a texbox in excel which has been assigned a macro. To make it clear I'm not talking about a textbox within a userform. I understand I should really be using command buttons but I prefer the look of textboxes.
I have the following code to change the colour of the textbox (name: txtCreateInvoice) but the final line to disable the textbox doesn't work.
Code:
Sheets(1).Shapes.Range(Array("txtCreateInvoice")).Select
With Selection.ShapeRange.Fill.ForeColour
.ObjectThemeColor = msoThemeColorText1
.Brightness = 0.5
End With
Selection.Enabled = False
View 4 Replies
View Related
Apr 24, 2009
On several of my worksheets, I'm asked if I want to disable/enable the macros but there are none associated with that particular worksheet- matter of fact, none of my worksheets have macros unless pivot tables count, so why am I being prompted in that way?
View 9 Replies
View Related
Dec 27, 2013
I have 3 checkboxes on a worksheet. I have a command button to copy/paste data that is captured from a device.
I want the data to go to the ranges that are selected by referring to the true/false driving mechanism of the check boxes.
Obviously if more than one button is checked at one time, the whole shooting match will blow up.
So....how can I allow only one check box to be active at any given time? I'm thinking perhaps a select case statement that refers to the possible conditions of true true true, true true false, true false true, etc....If that would work, how could the code be simplified?
Let's call them cb1, cb2 & cb3. Is this even possible??
View 5 Replies
View Related
Apr 17, 2008
Whenever I open an Excel file that has macros in it, a message always pop up and ask if I want to enable or disable the macros.
Is there a way to get rid of the message?
View 9 Replies
View Related
Oct 13, 2008
I started to write mini codes but still much to learn.
Private Sub ComboBox3_Change()
If Range("A1") = "TRUE" Then
ComboBox3.Enabled = 1
ElseIf Range("A1") = "FALSE" Then
ComboBox3.Disabled = 1
End If
End Sub
View 9 Replies
View Related
Feb 12, 2009
how to make txtBox1 only useable when optButton1 has been selected only.
I have 4 Textboxes
txtBox1
txtBox2
txtBox3
txtBox4
The following Option buttons are within a Frame called fraFrame1
optButton1
optButton2
optButton3
optButton4
View 9 Replies
View Related
Jun 21, 2008
I have a checkbox (checkbox1) on a userform that functions to disable two listboxes (monthListbox & yearListbox) when checked. For this, I have the following
Private Sub CheckBox1_Click()
MonthListBox.Enabled = Not CheckBox1
MonthListBox.BackColor = &H8000000B
MonthListBox.Locked = True
YearListBox.Enabled = Not CheckBox1
YearListBox.BackColor = &H8000000B
YearListBox.Locked = True
End Sub
This works fine and dandy. However, when testing the userform, when I "unchecked" the checkbox, it did not undo the actions. So my question is, is there a sub to undo the actions that the above code does after I "uncheck" the checkbox. Additionally...under what event would I write this sub since I've already completed the sub for Checkbox_Click()
View 3 Replies
View Related
Sep 30, 2008
I need a macro to disable all cut options (ctrl^x, right click cut, cut button in ribbon above), disable drag and drop, but still allow copying and pasting (the cutting affects cell references). I've already locked the spreadsheet, but users need to be able to input information and paste information in from other workbooks. This code must also only apply to THIS WORKBOOK and not affect others. Please help with a macro for this, as well as where to paste and how to properly configure macro security settings. I'm a big excel user, but this is my first time ever with VB and macros so talk to me like I'm dumb! I've pasted links to the two codes I tried, and I think one of them enabled a macro that affected all workbooks and now whatever code I put in won't save upon re-opening. It's there when I navigate to it, but it is ineffective like it's not even there. In macro security, I've enabled all macros and have the "trust VB programming" box checked. So maybe do some damage control before re-programming. I am getting a new computer in less than a month, but I want to be able to try out the code and have it work first, and I don't want to transfer the harmful code to the new computer.
P.S. This first code allows copying and cutting, but then just disables pasting, which is not what I want. The second one works great, but disables the right click menu altogether, which is no good either. And neither allow for pasting from the outside. And this is for moderate excel users, they won't try to erase the macro. I just need to keep them from making mistakes with cutting.
http://www.ozgrid.com/VBA/disable-cut-copy.htm
http://www.mrexcel.com/archive2/75500/87639.htm
View 14 Replies
View Related
Dec 24, 2008
I want to hide all toolbars and disable the red X upon opening of the spreadsheet, and to restore the main toolbars upon closing. That worked perfectly. (when I used the black code -see below)
I added in the red code as i wanted to disable the red X as well to ensure that users will always close the EXCEL worksheet via a Macro button.
The Macro will save the spreadsheet and close the worksheet and the Private sub should restore the toolbars.
View 7 Replies
View Related
Oct 14, 2009
How do I Enable/Disable Command Buttons from a Sheet?
I am using a Command Button in a Sheet to copy and paste the Data from one Sheet to another with the help of macro..
As I am not well-versed with VBA dont know all the syntaxes of VBA.
I need help for the command button..
First and Foremost, I double click a Command Button form the Control tool-box and paste it on the Sheet, I dont know how to get the name of this command button , I mean where do i get it?
Based on a condition like a value in a cell I want it to be Enabled and Disabled?
Any ideas...please I am not able to follow even after googling a lot as I dont know what's the name of the command button control I have used.
If the value entered in a particluar cell is more than the 1000 difference between two cells then the command button should be disbaled...
The Application part:
The command button is used to transfer the data in a cell lets say $I$4 to another sheet Cell J2,J3,J4 so on so forth..by incrementing the ROW number.
Now The balance gets depleted with every new Debit Entry and we need to disallow the user from entering such an amount which will reduce the balance more than The Minimum Account Balance of a bank...
View 14 Replies
View Related
Jun 13, 2009
I have collected some data from survey respondents, and I have the following array formula:
=MIN(
IF($C$1:$C$400="happy",
IF($G$1:$G$400="tall",
IF($H$1:$H$400="american",
$F$1:$F$400
))))
What I'd like to do is make the 3rd criterion -- $H$1:$H$400="american" -- dependent on whether the string value in cell A1 ("Evaluate Americans only?") is "yes" or "no".
Because, my actual situation is considerably more complex (see below), I'm looking to insert a test within the MIN array formula to check the value of A1, and calculate the answer accordingly.
I'm aware that I could theoretically create two array arguments (one with & one without the American test), and place them inside an IF function that tests the value of A1. However that isn't practical for my real-world situation...
I occasionally see array formulas with all sorts of symbols that I don't really understand in the array context (such as * and | and . ), and I'm wondering if one of those magic symbols might be the doorway to my solution.
View 5 Replies
View Related
Jun 20, 2014
Is it possible to disable and ActiveX listbox, but allow the scrollbar to work?
Or, prevent selections without changing the enabled property?
I'd like to populate my list with items that are for information purposes only.
View 8 Replies
View Related
Mar 13, 2008
I am trying to allow uses of a spreadsheet to be able to select one control button which would in turn disable another one via check boxes however the control buttons do not re-enable when the undo check box is selected.
Sub Lead_REC()
' Set screen behaviour
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
' Defer error handling
On Error Resume Next
Err.Clear
View 3 Replies
View Related
Jun 19, 2008
I've created a macro to disable cut-copy-paste and drag-and-drop (below), but when the user exits Excel, the drag-and-drop option remains turned off.
The user has to click on the Office button, go to Excel Options, Advanced tab, and re-enable the fill handle manually. How can I have my macro do this automatically when the workbook is closed?
'*** In a standard module ***
Option Explicit
Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Call EnableMenuItem(21, Allow) ' cut
Call EnableMenuItem(19, Allow) ' copy
Call EnableMenuItem(22, Allow) ' paste
Call EnableMenuItem(755, Allow) ' pastespecial
'Activate/deactivate drag and drop ability
Application.CellDragAndDrop = Allow
'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
With Application
Select Case Allow
Case Is = False ....................................
View 9 Replies
View Related
Jul 18, 2012
How to disable different parts of a userform?
I dont really want to go through each control as I reckon it will be handy code to have in the future if I need to add more but so far my code is:
Private Sub UserForm_Activate()
With Sheet2
n = 3
[Code].....
View 2 Replies
View Related
May 11, 2007
I have two radio buttons. Let's call them Button_1 and Button_2. They both have code behind the click event.
One of the lines in the Button_1 code makes the value of Button_2 false. If Button_2's value at the time is true, the Button_2 click event fires off and runs through it's code.
How do I disable Button_2's click event in this scenario so the value changes, but the code in it's click event won't run?
I tried application.enableevents = false, but that didn't do it.
View 9 Replies
View Related
Nov 18, 2009
I have two linked ComboBoxes on a form. There are 10 Textboxes populated by ComboBox2’s 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 Combo1’s value is changed. ( Combo1 is a filtered key list using the dictionary. Script code)
I’ve 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 aren’t cleared. In fact it will show the “Walnuts” info in Combo2.
View 4 Replies
View Related
Jun 8, 2007
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
View 9 Replies
View Related
Oct 24, 2007
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.
View 9 Replies
View Related
Mar 12, 2007
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
View 4 Replies
View Related
Apr 28, 2014
I cant get this to work, it comes back with run-time error '424': object required
I am using form controls option button not activeX controls.
HTML Code:Â
Sub OptionButton12_Click()
If OptionButton12.Value = True Then Range("D14").Value = "Alamo"
End Sub
Sub OptionButton13_Click()
If OptionButton13.Value = True Then Range("D14").Value = "ERAC"
End Sub
Sub OptionButton14_Click()
If OptionButton14.Value = True Then Range("D14").Value = "National"
End Sub
View 4 Replies
View Related
Jul 21, 2014
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?
View 7 Replies
View Related
Oct 3, 2006
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.
View 2 Replies
View Related
Mar 18, 2008
i know in VB6 i could disable the X in the corner so someone can't X out of my form, and i see that's not an option in VBA.
So how can i stop the form from terminating when the user clicks that? i want them to be forced to use one of my buttons to close out and finish.
i tried adding something simple like Exit Sub to the Userform_Terminate() sub, but nothing. i can make code run when they click that, but i can't stop it from closing. what to do?
also, the form is being called by macro that does alot of other stuff before and after the form, so just opening a new instance of the form won't help unless you can stop my other code from continueing until the form is actually done...
View 9 Replies
View Related
