I'm making a worksheet with 64 dropdown boxes.
I would love a macro that activates whenever any combo box is changed, that could tell me which DropDown was changed.
DropDown1_Click() seems to be the only option i can find, but is there a way to not have to create a unique method for each object?
I have created a workbook of 12 monthly worksheets, with an index page with hyperlinks to each worksheet. There is also a hyperlink on each worksheet to return to index page. Each worksheet is protected, but the cells which users can enter information are not locked. The protected worksheets have the 'select locked cells' unticked.
I have found that an inadvertant click on any of the protected cells in the worksheet will take users back to the index page. This is very annoying, what have I done wrong? The hyperlink is one of the unlocked cells.
I have a worksheet that serves as a navigation page for the workbook and use the following code to restrict from scrolling off the viewable content. The problem is that the code doesn't kick in until either a click if the mouse or keyboard input. This initially leaves the worksheet open for scrolling until input is received.
How do I make this code activate when the wb is loaded? Code in the Thisworkbook module already makes the menu the visible sheet, and code limiting the scroll area is in the sheet module.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
.ScrollArea = "A1:L11"
End With
Range("A1").Activate
End Sub
I've also tried using "Range("A1").Select", but it doesn't work either. How can I get this to work correctly?
I use Excel 2003. With a LOT of help from this board, I've written some macros for a workbook template that will be opened then saved with another name with an .xls extension. The problem is that the macros transfer data to and from another workbook template. Here's the code that's giving me problems.
Windows("BookA.xlt").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("BookB.xlt").Activate
How can I use the macro if I open BookA.xlt then change the name to BookZ.xls for my data entries? At the same time BookZ is open, I may use another instance of BookA.xlt and save that one as BookY.xls. What's the most feasible way to still use the macros?
The following code works fine but now I want it to work without a userform. SO there is no userform and so no controls.
When the user changes date in a cell then a macro should run and following kind of code should execute.
[Code] .......
how to alter this Private Sub for my code to execute automatically once all 3 cells have changed. At the moment it executes as soon as any of the 3 cells change.
Here's the Private and Public Sub codes:
How do I have a macro execute anytime a checkbox, or radio button is clicked on a form? I have a macro that needs to run, but I don't want to put it in the code for every checkbox because it will complicate any changes or additions to the form.
View 9 Replies View RelatedI have a worksheet containing a cell (say A1) that has a validated list of times (data/validation/list) where the cells in the list contain times in 8 minute increments. The user can select cell A1 and manually select any of the validated times from the list. A vlookup formula on this worksheet uses the content of cell A1 to look for this time in another section of the worksheet. When the time is selected manually, the vlookup formula works properly.
As part of a macro, the macro will change the time in cell A1. I do this in the macro by selecting and copying the desired time from the validation list and pasting it into A1. Cell A1 does change to the new value, however the vlookup formula that uses A1 shows a result of #N/A. If I manually change the time, it again works. I am looking for a way to have the macro change this cell to another time and for the vlookup formula to work as it should.
I would like to create a macro that will automatically change the name of the sheet any time a value is entered into cell a1. For example, in cell A1 of sheet1 I would input "Hello", and then the tab for sheet1 would be automatically renamed to "Hello".
Here's the twist - the workbook will have multiple sheets, and I want all tabs to reflect the value of a specific cell (a1) in each sheet.
For example:
value in sheet1, cell a1 = "Yes" - corresponding tab name would change to "Yes"
value in sheet2, cell a1 = "No" - corresponding tab name would change to "No"
value in sheet3, cell a1 = "Maybe" - corresponding tab name would change to "Maybe"
etc...
The "tab change" cell would always be cell a1 in each sheet.
Colouring calendar entries based on a date match... VBA to colour a cell on each line based on date match
The code works fine if you change the date in the cell and press enter however my date changes are triggered via a Vlookup which the code doesn't recognise has changed so doesn't trigger the macro.
Is there any way to amend the VBA code shown below so that it triggers the macro when dates are changed in column I via Vlookup?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Target.EntireRow.Interior.ColorIndex = xlNone
[Code] ......
- A database sheet called 'QAEQUIP' which contains information on the movements of pieces of equipment. This information in in order of Column A which is a movements reference number.
- A reporting sheet called 'Email sheet', which you put in a month and year and using macros it copy and pastes the relevant rows from the database and puts them in date order (e.g. put in October 2013 and it will show all of the equipment moves that happened in October 2013, and in date order).
What my end user would like, is to be able to add text to an extra column on the reporting page which gets fed into the database on the corresponding row. What would the best way to do this be? I was thinking VBA for each row (there aren't that many rows) that says if this cell is changed, copy and paste the cell into the corresponding row / column on 'QAEQUIP'.
I have found a macro that should work with a change of a specific cell.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$7" Then
Call Button
End If
End Sub
However, the code doesn t work as with a change of A7 nothing happens.
A7 equals to result from a drop down list --> A7=A5(which is a drop down list value). The "Button" macro is a combination of 4 macros that get rid of zero values and names in pie charts (maybe this plays any role). The master macro is assigned to an object. The code is:
Sub Button1()
Sheets("report").Select
ActiveSheet.ChartObjects("Chart 140").Activate
ActiveChart.SeriesCollection(1).ApplyDataLabels AutoText:=True, LegendKey:= _
False, HasLeaderLines:=True, ShowSeriesName:=False, ShowCategoryName:= _
True, ShowValue:=True, ShowPercentage:=False, ShowBubbleSize:=False, _
Separator:=" "...................
I am using INDIRECT to create a second dynamic drop down list (L1) that is based on the cell contents of a first drop down list (I1). I want to create a macro that will blank the 2nd drop down list ONLY when the choice in the first list is CHANGED. I have the following, but it blanks the second list as soon as the first list is clicked on, rather than when a change is made:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Range("I1"), Target) Is Nothing Then Range("L1").ClearContents
End Sub
When I use customize and assign a macro (to a button) I set the "Marco In" option to "This Workbook" then select a macro from the available list. If you close the Assign Macro dialog and open it again, notice how it appends the name of the file to the macro name. Later, in an automated process the xls filename is appended with a value and I think this is causing the problem because the appended data is static and thus no longer is in sync with the new (latest) filename. How do I assign macro's to either custom toolbar's or autoshapes so that the assignment is tolerant of file name changes? I tried to delete the appended data but it is appended automatically.
I am also curious why some of the macro's listed in the dialog include a filename and macro name delimited with an exclaimation mark and some are macro name only (with no delimiter).
I used the "record macro" to record a simple sorting (high to low) of columns A-F based on values (0-3) in column A. Everything works when I initially institute the macro. However, when I change anything or add a new item at the end of the list, the order reverses and sorting goes from lowest to highest.?
Here is what ended up in the macro:
Code:
Sub SortColumnA()
' SortColumnA Macro
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
[Code]....
I noticed that it only includes a fixed value for the length of the list (row 90). Is there a way to have it increase when anything new is added?
As of now I plan on just making an arbitrary large index (~200 ish) to automatically grab anything new.
I need to save a document in the following path: [URL] .......
Only the year month and day sould change when I hit the save button/macro: 2014_01_29
How to build a macro that would save my file as per my above explanation??
If I would run the macro today the excel file should be saved in the path: [URL] .......
I have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working.
Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$16"
Select Case Target
Case "Custom Color 1": Call CustomColorInput1
Case "Custom Color 2": Call CustomColorInput1
Case "Custom Color 3": Call CustomColorInput1
Case "Custom Color 4": Call CustomColorInput1
Case Else:
End Select
Case "$A$17"
Select Case Target
Case "Custom Color 1": Call CustomColorInput2
Case "Custom Color 2": Call CustomColorInput2
Case "Custom Color 3": Call CustomColorInput2
Case "Custom Color 4": Call CustomColorInput2
Case Else:
End Select
Case "$A$18"
CustomColorInput 1-6 are the same except they input data into different cells F16-F21.
When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code.
Function PullAfterLast(rCell As Range, strLast As String)
PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
End Function
When I close VBA, the data that I typed into the data input box is in the correct cell.
So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on?
More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
I have seen files in which when you click on a specific tab insted of showing whats on that tab it redirects you to another specific tab next to that first tab.
View 3 Replies View RelatedI want activation of userform to hide worksheet, but as soon as the Userform is closed, the worksheet should show.
I have attached file to aid.
I have a forecast spreadsheet with 10 data tabs and a summary tab.
On the summary tab, cell R131 contains the year end profit figure.
What I would like to do, is whenever this value changes and I am in a different tab, a small pop up appears in the current tab telling me what the R131 value has changed by. eg "Profit up £1000"
At the moment I keep having to go back to the summary tab to see the effects of changes across the whole spreadsheet.
If the value of any cell in in column D is changed to "Closed", a message box will appear that says "Others must be notified when item is closed". In that message box, I want two buttons: Button 1 One is cancel and will undo the change. Button 2 will open MS Outlook with pre-filled email addresses and will attach the open worksheet. After the email is sent, to return to Excel.
View 4 Replies View Relatedwhen the name changed and sum changed.
View 9 Replies View RelatedI have created a userform with multi pages... One of the page is up and running ok.
However I have on one page entries like customer number, name, address, and so on.
I have the same userform from a book which I like to use in one of my pages.
Now what I have problems with is that I need one page to refer to another worksheet of the workbook.
Lets say first page to one worksheet the second page to another. And so on.
Here is the code from the book and it likes to refer to the same worksheet as the userform is opening up.
Private Sub frmDFG_Initialize()
'Startzeile für das Drehfeld festlegen
If .ActiveCell.Row < 3 Then
spn_Change = 3
Else
spn_Change = ActiveCell.Row
End If
End Sub
I have the following code on my worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
Do_it
End If
End Sub
Cell B2 contains an employee number. What I need to do is save some data for the employee number that was in cell B2 just before it was changed.
Example: If employee number "10" is entered in B2.
Then the user were to change the employee number in cell B2 to "55" I would like to use the previous value "10" to do some VBA stuff with.
Excel has went from using the backslash to using a hyphen. If I try to format the cell, all the date choices use the hyphen, what possibly changed?
View 3 Replies View RelatedIs it possible to save a workbook whenever any changes are made to any cell in a specific column? The reason - I'm putting an excel workbook out on a network drive for 4-5people to share. The sheet contains work items which are imported every 4 hours... Column A contains the status.
I want to save the file every time anybody changes a status to prevent multiple users from working on the same item. I realize that I could build a form then load each workitem into the form, then apply the save to a change event on the field/control, but that's something for next week - I need to get this out the door, bare bones, as soon as possible.
existing workbook that has evolved over several years, there are bits of data spread all over the sheets and there are several sheets.
My question is. Is there a way to show/print/email an alert with a reminder message any time any of the formula cells gets changed, something like "Be aware a formula is being changed, make a note of the changes."
In my spreadsheet I have column B which has a drop down list using "open" and "closed" as the choices. Whenever someone selects the closed option, I would like for that row to be moved to the next blank row on the tab called CLOSED. Is this possible? I was thinking that if it was it would require some kind of coding. I have attached my spreadsheet for you to look at.
View 2 Replies View RelatedI chose to place this in programming, as all the functions for this workbook are either macros or Internet Links. Nothing is ever changed on the worksheet.
I have a workbook that is fully protected with passwords both for the workbook and the worksheet. When I set up the protection, no check boxes are checked.
I have macros and internet links on the worksheet that can be selected, but none of them affect the worksheet.
As a matter of fact, I can open the workbook and close it with only "Application.Quit" in a macro and a message box comes up asking if I want to save the changes or not. If I close it with the upper right corner RED X, it still asks the question.
I have no "On Open Macros" or Links of any kind.
I have other workbooks that this does not happen on.
This is the first VBA project i've done on Excel, and its not very big. I've never done any programming before, but i need help on this
What i'm trying to do it to have the Combo box that when an item is selected the respective picture shows next to it (i've got a seperate sheet with the products, prices, pics etc...)
After trying everything to get the picture to show, i switched to VBA because it looked like i could do it in that. The first thing i did was record a macro for each picture - the first one goes like this (its called TEST)