Code:
Private Sub Workbook_Activate()
Application.CommandBars("Ply").Enabled = False
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"",FALSE)"
End Sub
Code:
Private Sub Workbook_Deactivate()
Application.CommandBars("Ply").Enabled = True
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""RIBBON"",TRUE)"
End Sub
All of a sudden my right-click isn't enabling when I reopen Excel. I can run the macro
I want the user of a file to be able to click on a particular cell and if they right click an option 'Change' will appear on the list of options and on clicking it a macro is enabled.
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.
I have placed the following code in my spreadsheet but it's not working!
the code after option explicit (now at the bottom) works, (thanks to Andy on here!) but all the disabling the right click and cut/copy/paste etc doesn't.
the following code works well i have a range of cells(e1:o17) i click on one of those cells to enter a score into (a2) the next cell i click enters a score into (b2) ok so far but if i need to enter the same score consecutively as needs to be done from time to time i cant do it because the cell is still active.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("d1") = Range("d1") + 1 If Range("D1") = 2 Then Range("a2") = ActiveCell.Value: Range("c2") = 501 - Range("a2") If Range("D1") = 3 Then Range("a3") = ActiveCell.Value: Range("c3") = Range("c2") - Range("a3") If Range("D1") = 4 Then Range("a4") = ActiveCell.Value: Range("c4") = Range("c3") - Range("a4") If Range("D1") = 5 Then Range("a5") = ActiveCell.Value: Range("c5") = Range("c4") - Range("a5")
I have an excel sheet with date as the first column and all other data being numeric. I put in a code to increment by 1 everytime the user clicks on a cell, the contents within it. the code is below.
VB:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Range("B2:H100")) Is Nothing Then Cancel = True Range(Target.Address).Value = Val(Range(Target.Address).Value) + 1 End If End Sub
This does its job for the time being, but I want to allow changes only to the row which has the current date and deactivate all other rows for any editing. So that if I click anywhere else but the row with the current date nothing happens.
I have some code in the BeforeClose event of the workbook - it worked the first time I tried to shut down the wb, but never since. I opened a new wb and copied in the code and again, it worked first time, but not since. The code is just calling a function, nothing heavy. I've tried the Deactivate event as well. I'm more of an Access VBA kind of gal, so don't know if I'm missing something big here.
I need to perform an action (unhide all columns) whenever I switch to a different worksheet.
Detail: I have a file with 2 tabs (worksheets). Tab 1 contains daily data, and when people are done with the current week they group and hide the columns for that week. This allows them to only view the new week and do an easy copy/paste into other applications. Tab 2 totals up the daily data from Tab 1 and shows monthly totals. The problem is that when they hide Tab 1 columns for past days, the formulas in Tab 2 don't "find" that hidden data. I would like to write a basic code that unhides all Tab 1 columns when I switch to Tab 2 so the formulas on Tab 2 reflect accurate totals. My thought is that it would be Worksheet code on Tab 1 using "Deactivate". I've tried to piece together different bits of code but can't get anything to work properly. I don't want them to have to run a macro or click a button, I'd like it to be automated when they switch tabs.
The following code does a sort on a worksheet whenever you click another sheet but it also returns you to the original sheet (where the sort takes place i.e. the sheet with Range "Sort1"). How do I get the code to do the sort but not keep returning to the sheet?
Private Sub Worksheet_Deactivate() Application.Goto Reference:="Sort1" Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, End Sub
I have an Offset of a range I am testing the value of with an If statement.
For some reason, even though the value fed in is 1 and it should bypass the If statement, it doesn't. However, if I assign the offset value to a variable first, the If statement works...
I have a number of buttons on a daashboard that run different macros.
Is it possible to have the most of the buttons deactivated until a cell on a different sheet has a value in the there (Text or number) turning them active?
The following code for a custom menu is used in a workbook which has two sheets with embedded charts, two chart sheets and several sheets for calculations and information
In This Workbook
Private Sub Workbook_Activate() 'Changed Activate to Open Run "AddMenus" End Sub
and
Private Sub Workbook_Deactivate() 'Changed Deactivate to Close Run "DeleteMenu" End Sub
and
Private Sub Workbook_BeforeClose(Cancel As Boolean) Run "DeleteMenu" End Sub
I'm trying to do something quite simple but I can't seem to see the forest thru the trees.
All I want to do is when I deactivate a worksheet (sheet2) I want it to sort a predfined range. I keep getting a "Select Method of Range Class Failed" error message on the line of code with ******
I have a survey template with several options buttons (made from Form toolbar and grouped by Group Box).I would like to have a check box that would activate these option buttons for specific question if unchecked and vice versa.
I have copy & paste below formula in VB, but is shows 'Run time error 424' & when I Debug it shows 2nd line highlighted by yellow colour.
VB: Private Sub CheckBox1_Click() If CheckBox1.Value = True Then [code]....
I have excel sheets that cross link with each other. I've just migrated to Office 2007. When I open my excel sheet I always get the question at the bottom of the tool bar Security Warning Data connections have been disabled. And then I have to click on option and enable content.
My question is, how do I always enable the content?
I have a workbook which uses data connections to our sage accounts product. Everytime i open the sheet i get the "Data Connections have been disabled" security message, and need to go in and enable them. Is there any way of enabling the data connections by default? I only want to do it for this one workbook - i still want to be prompted when i open other workbooks.
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
I have a spreadsheet with a web data import query that is set to automatically refresh whenever I open the spreadsheet,
But whenever I open the spreadsheet, I get a dialog box "Query Refresh" that tells me my workbook contains queries to external data that refresh automatically.. etc., etc. This requires that I click "Enable automatic refresh" to get close the dialog box and start the import.
I want to get rid of the warning box, and just have it run after being started.
I have converted an Excel file from 2003 to 2007. The file now has a .xlsm extension as it contains macros. I have "enabled all macros" and "trusted access to the VBA project object model". Whilst open, I have tested the macro buttons and they work.
I then close the file and re-open it, and the macro's will not work. A message appears along the lines of "macros are anabled" or "macro is not found". I have double-checked the settings and everything appears normal.
Is it possible to add a piece of code with the below code.
There are two Buttons in my Work Sheet. One "SAVE" the other "PRINT". If PRINT button is clicked by mistake before clicking SAVE button, a Pop up message should say "SAVE DATA FIRST". If Data is saved then based on that I should be able to Click PRINT Button.
( Or )
Enable PRINT Button only after DATA is Saved
Sub saveit() With Sheets(2) r = .Range("B65536").End(xlUp).Row + 1 InvN = Cells(15, 4).Text
'Clicking the "SAVE" button Without entering datas in the rest of the Fields, 'ie., in C18, C20, C22 and C24 should NOT save the Current Invoice number 'with the rest of the details in the next sheet. A pop up should say that '"Fill all Fields" when trying to hit the Save button if a Field is blank.
If Range("c18") = "" Or Range("c20") = "" Or Range("c20") = "" Or Range("c22") = "" Or
Range("c24") = "" Then MsgBox "Please fill all required fields", vbCritical, "Missing data"
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?
I have a ton of text boxes on a userform that are in an initial state of being disabled. Is there a quick way of enabling by use of a mask for these Text Boxes ?
All the text boxes I want to enable are named "TB_fieldname" and by mask I mean a snippet of code using something like "TB_*"
Example tb_liqname, tb_casecost, tb_bottleprice etc etc..
I saved the workbook as Macro enabled workbook in 2007. Also changed the security level to 'Enable all macro'. But the Macro buttons on the developer tab still shows disabled. Also not able to open VBA window with Alt+F11 key.
