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 RepliesI 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??
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
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()
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'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...................
how can we disable/enable a cell based on a condition which is dependent on other cell data
View 4 Replies View Relatedi want to dis able and enable this button using three criteria, and auto paste the comment to other sheet.
View 2 Replies View RelatedIs 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
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 RelatedWhenever 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?
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
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
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.
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...
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.
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.
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
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 ....................................
I have a sheet with a number of checkboxes, and I need to conditionally enable/disable some of them.
For example, if the user checks Box1, then the question for Box2 becomes relevant and Box2 becomes available. If Box1 is unchecked, Box2 needs to be unchecked and disabled.
I want to have a Checkbox (Form) that is in my Worksheet to disable/gray-out once it is selected. I don't want people to be able to uncheck it again. I want them to be forced to click another Checkbox to enable it again.
Example:
Two Checkboxes:
Check Box 1 = "Apply"
Check Box 2 = "Delete"
Once "Apply" is checked, gray it out. This will force a user to click "Delete" in order for the "Apply" button to be enabled again. I do not want a user to click "Apply" once selected as their way of 'deleting' the information.
I am good at excel but an infant with VBA. I have inserted a checkbox onto my worksheet. I want it, when checked, to select a row of data ("k5:k16") from Sheet 1 and copy it to ("a1:a12") in Sheet 5. When the checkbox is unselected, I want the ("a1:a12") to be cleared. I've tried several different ways to copy and continue to run into errors. I know there is a simple solution, but it's beyond me.
View 2 Replies View RelatedI am quite new on this forum.
I would like a dynamic range to be multiplied with 2. The Range is always start in cell H14 to P?
I have multi checkbox in column F,
So here is an eksampel: If checkbox in F4 is true then Range H4:P4, should by multiplied with 2 else if the chekbox is FALSE, then only multiply with 1.
I want to entry data in the Range H14:P? by this code
Private Sub Worksheet_Change(ByVal Target As Range)
'Do nothing if more than one cell is changed or content deleted
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
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 RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
I have the following script in a command button. the command button is for the month of january. I would like to enable the button based on if a cell value is January, or unenable it based on the cell value being any month after January....
View 7 Replies View RelatedI have a spreadsheet with 2 combo boxes - Combobox 1 enables a user to specify if they want to look at the data on the sheet by week or month - it has just 2 options - "select by week" or "select by month"
The list in combobox 2 changes based on the selection in combobox1 - so if you select "select by week" , a date list by week shows up.
Combobox 2 is supposed to run a macro which updates some backend pivot tables. there are 2 types of backend pivottable - weekly tables and monthly tables.
What i am not sure about is how to get the selections in combobox 2 to run either the weekly macro or the monthly macro based on if combobox1 is set to week or month ...?
Have wrote the following code to enable 7 sheets to be unprotected, the pivot tables and charts housed within them to be refreshed then the sheets to be protected. However, once the macro has finished I need to be able to still use the filters on the pivots to flick through data. I also need the data table to be protected with the exception of a cell range that can be edited (preferably password protected.
Sub Refresh()
'
' Refresh Macro
' Refresh Pivot Tables/Charts
[Code].....
This is what i have but its not working.
Code:
Private Sub UserForm_Initialize()
CommandButton4.Enabled = Not (Sheets("0").Range("J8") = "Off")
End Sub
I have a worksheet with several rows (A3:H70). Column J contains checkboxes (1 in each row). I'm trying to develop a macro that will clear rows based on the value of the check boxes. For example, if the checkbox on J3 is checked, that row (A3:H3) will be cleared.
If the checkbox on J4 is UNchecked, then that row will be skipped and so on.