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.
I have a workbook with the following:
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
Code:
Application.CommandBars("Ply").Enabled = True
once opened, but once I close it out again, the right-click is disabled.
is it possible to deactivate an activecell?
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.
View 5 Replies View RelatedI 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...
This does not work...
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 ******
The code is as follows: ...
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 was wondering if there is any way to "grey-out" a commandbutton? I made it using the forms toolbar.
View 3 Replies View RelatedI am trying to code a macro to run down through a column and hide the row if it finds a cetain marker (i.e. the letter "n") in the column, and then move onto the next one.
View 9 Replies View RelatedMy spreadsheet calculates the date on a daily basis.
I would like to create a code in my spreadsheet which uses the year and month in question.
First, 2006 is is designated as a "3"
2007 "4"
2008 "5"
2009 "6" etc
Don't worry about anything prior to 2006.
Next, the months are represented by single digits. So, January = 1, February = 2, March = 3, April =4, etc. September is 9, and October = 10 but all the numbers over 9 have to be reduced to a single digit, so October = 1+0=1, November =1+1= 2, December =1+2= 3.
Now, the formula for the Month is:
Year + current Month
So, May 2007 = 4 + 5 = 9
June 2007 = 4 + 6 = 10 = 1
July 2007 = 4 + 7 = 11 = 2,
January 2008 = 5 + 1 = 6, etc
Is there a way of coding a macro in VBA that allows you, with one macro, to enter text in one cell and at the same time enter a value to the cell on it's right?
I don't want to name the cells, but I wondered if you could add to the code a piece of code that basically said "Select the cell to this cell's right and add the value x".
I have made this vba to go out and look at 2 different locations which contain simular folder names.
The script then writes the folder names in Column A, followed by the Size of both the folders from the 2 locations. The script is comparing the second location folder name to what the first process put in Column A so the folder names will be aligned with the file sizes. It is also converting the file sizes from bytes to the most appropriate one.
The problem I am having here, is once I put the second part in for the second folder, the script is either taking forver (More than an hour) or is freezing. So I am not sure if I put it in a loop somewhere or what exactly is happening. I am just wondering if I can get some help troubleshooting and optimizing this code....
How it is possible to lock the VBA coding and Modules? I mean when Some one want to view VBA coding the Password window should not appear, istead of that a message box appears with message something like "Not viewable"
View 9 Replies View RelatedI have a workbook in excel 2003 which I had been running the following macros (listed below). We recently upgraded to Excel 07, and neither are working. When I try to run them, the "debug" option highlights the following line in the sort macro "Range("A2:z" & lastcell).Sort key1:=.Columns(1)". This is driving me crazy, as the macros worked perfectly under the older version of Microsoft. Is there an issue with crossfunctionality between '03 and '07'.
Private Sub Worksheet_change(ByVal target As Excel.Range)
If target.Column = 1 Then
ThisRow = target.Row
startRow = 1
i = 1
Set ws = ActiveSheet
maxRow = Cells.SpecialCells(xlLastCell).Row
maxCol = Cells.SpecialCells(xlLastCell).Column
ActiveSheet.UsedRange.Interior.ColorIndex = xlNone
Do While i
I have a drop down box with the 10 different shifts we run here at my work. Ie - Daytime (0700-1500), Nightime (1500-2300) etc. I have these on a drop box as stated earlier (Cell 6) - and want to code a selection from within the box, to effect Cell 7 with the approriate hours worked depending on what I selected in Cell 6.
View 2 Replies View RelatedI am looking to create coding that once stock gets to a particular level a text box pops up to alert the user that more stock needs to be ordered and ideally I would like once the user selects ok for it to take them directly to the appropriate email template.
However, I don't want the text box to come up while we are waiting on the stock to be ordered.
i.e our current minimum stock level before placing our next order is 10,000 units. Order is placed and can be received within 10 - 14 days. During this time our 10,000 units will be used but I don't want an alert to pop up to remind the user to place an order as this action will already have been carried out.
I'm trying to write a code which would place formulas in the row just below the my range (normally a matrix of figures) to sum the column in that just above that. Here's what I've got so far but it doesn't work.
View 6 Replies View RelatedI am writing VBA code to do the following:
IF A1 > 50 then print array C1:E7
I have this code and it works.
when the accounts exported to excel, all the tabs shown in the file are generated. However, i need to change tab '3' to tab '10' name to the name stated in tab 'menu'. eg tab 1 need to be renamed as 'BB' (not BB/Bunut), tab 2 to 'GEN' (not GEN/General), etc. All must be capital letter.
View 5 Replies View RelatedI have table where i have to find a value based on 2 criterias. So i have used in Excel the below formula:
=INDEX($A$1:$D$7,MATCH(1,($A$1:$A$7=$F2)*($B$1:$B$7
I was wondering if there is a workaround for sorting in Excel by MORE THEN 3 CRITERIA?
I have seen a couple of solution doing 2 seperate sorts but was wondering if you can write code to expand on Excels limited sorting capabilities?
i'm trying to figure out a formula that if a cell is greater or equal to another cell - color a cell "GREEn"
otherwise...color the cell "red"
I am trying to figure out how to run a macro for 1 particular workbook in an excel spreadsheet. I don't want it run on any of the other workbooks in that file, just the 1. I can't use conditional formatting because I need more than 3 values (if statements).
This is what one example of data in a cell and below is the color I'd like it to turn when I run the macro:
T: 6/1/07
A: 6/8/07
Anything that has a A: (which means an actual date it happened) I'd like the cell to turn blue. If there is an RT: (which means revised target) I'd like the cell to turn red which means it missed it's target date, and has been revised. If it has a TBD I'd like the cell to turn pink (or yellow or any color really). All other cells are just white. I don't know where to find the color codes in excel as well. Other samples of what cells look like are below.
Here is another example:
T: TBD
And final example:
T: 6/4/07
RT: 6/15/07
where can I get vba coding for a simple clock.
I would like the clock to be digital rather than analogue if possible
I am trying to learn how to use it in some of my Excel application. I am just wondering if someone would be willing to help what I am thinking would be a short string of code.
What I have is a list of files that I have compiled along with a name and description in cells B3 and C3 respectively. In cell E3 what I have is the path to that file which is linked so that I can click on it to access the file. Is there any way that I can write some kind of simple code to look up the last modified date of that file whose path is specifed and place it in cell D3.