I have a macro that is run by clicking on a macro button. The macro copies the data from Sheet1 and pastes it in another sheet, Sheet2. I added some conditional formatting that colors certain cells red if others are blank on Sheet1. I would like to add some code to my macro that will not allow it to copy and paste from sheet1 to sheet2 if there are any red cells in the range.
View 9 RepliesI need a line of code so that when I hit commandbutton2, the Private Sub Worksheet_Change(ByVal Target As Range) event macro on the same page DOESN'T run. The button clears certain lines, and when it runs the change event it ends up in an error, and I don't need it to run when hitting the commandbutton.
View 6 Replies View RelatedI have made a vba program in excel 2003 that opens a worksheet using Workbooks.Open, and copies all the worksheets out into the program etc. The worksheets that I am opening have macros that automatically execute when the worksheet is opened (opening up some forms ). These macros are running when I open the file using VBA. How can I prevent this from happening. The automatically executed code in the workbook being opened is located in "this workbook" and can be seen below.
Private Sub Workbook_Open()
Dim CfileName As String
'Check Config sheet for template state
If Config.Cells(10, 3) = 1 Then
CustomizeDirInfo.Show
End If
If Config.Cells(10, 3) = 2 Then
GetConfData
GetGrpData
StdFileGen.Show
Else
Exit Sub
End If
End Sub
I know how to enable/disable events using VBA code, however is there an option within excel to turn it on/off? My problem is this...
At the beginning of my code I disable events and at the end I enable it again (I need to do this to avoid being caught in a loop). However something is going wrong somewhere in my code and the code stops halfway through. I'm trying to test sections of the code, but I often inadvertently stop the code without enabling the events again. Therefore I can't get my VBA to execute again unless I close excel down and restart. This is a pain as I have to find my place in the code again!
I have this excel file with some functions inside a Pivot Table, which use a year value as filter (the value comes from a report filter field) and data from a different spreadsheet to calculate a percentile value ... I created custom functions to replace the excel functions, passing the values as parameters.. The problem is now that when we refresh the connection (Refresh All button under DATA tab) the cells that contain the values returned by the custom function lose those values (go blank) In order to get the values I need to select the filter value again, while when I was using the normal PERCENTILE function the values would always refresh normally
Now... I think the problem might be related to the fact that the spreadsheet in question calls 5 different custom functions at the same time.... and, those 5 function call another at least two more helper functions... so, maybe this recurrence in calling the functions is causing the thing to break up
Is there a way I can prevent this from happening? Maybe put some flag or something that will cause the functions to be executed one by one, and not all at the same time?
I am having difficulties with my Worksheet_Activate() macro. It works great within workbook1 when it is only workbook1 open - but when I open another workbook2, the macro stills runs, presumably because Sheet1 of workbook1 is still activated as well as the newly activated sheet in workbook2.
Is there a way to ensure that only 1 worksheet of 1 workbook is activated at a time? Or that sheet1 of workbook1 is deactivated when workbook2 is opened/clicked on? I need my Worksheet_Deactivate macro to run to get rid of my Worksheet_Activate macro (which runs an application that resets the function of keyboards keys). Otherwise moving around workbook2 is a nightmare. When I navigate back to workbook1 while workbook2 is still open, I still want sheet1 of workbook1 to be activated and my macro to run .
I have a piece of code that hides unneeded rows, it does work but it is very slow.
This is in the worksheet part of the project. My problem is as it runs it "jumps" in to a function I have in a module that counts continuous rows. I would like to know why it is doing this and what I need to do to stop it? I have another piece of code that is structered exactly the same that hides unneeded columns and I do not have any problems with that. I know both pieces of code are dealing with rows and I think this is part of the problem but I can not see any reason why the first bit would call the second bit?
Dim C
With Worksheets("CEN OAS"). Range("D5:D378")
.EntireRow.Hidden = False
End With
For Each C In Worksheets("CEN OAS").Range("D5:D378")
If C.Value = "" Then
C.EntireRow.Hidden = True
End If
Next C
Function to count used rows....................
I have a userform with a combobox in it that's rowsource is a column of company names in a worksheet. When a company name is chosen, a combobox change private sub runs and many userform textboxes are populated with information about the company that was chosen from the combobox dropdown. This information is stored on a worksheet. I then want to edit any of the information in these textboxes. Once my edits are made I have a CommandButton that is pressed to save the edit changes. This CommandButton runs a private sub that disables the combobox (thinking this would prevent the combobox private sub from running), deletes the row that the information originated from, and then SHOULD make a new row of values based on the contents of the textboxes following the edits. The problem is that the CommandButton coding that deletes the row causes the combobox change private sub to run because the company that had been selected is now the missing from the rowsource; this causes an error.
View 8 Replies View RelatedI would like to write a macro that performs the following function:
I have a value in cell A1
In cell A2, I have the formula "=A1"
I want to make it so that cell A2 can never be directly changed; the only way to change the value in A2 is by changing A1. A2 should always equal A1.
I want a message box to pop up and alert the user whenever they attempt to modify A2, instructing them that if they wish to change A2, they have to change A1.
I had written this macro, and it kinda does the trick:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a2") Range("a1") Then
MsgBox "text goes here"
Range("a2").Value = Range("a1")
End If
End Sub
When I attempt to change cell A2, I get the MsgBox popup and it works how I want it to. However, when I alter cell A1, I get the same popup before cell A2 updates (to be precise, the popup occurs after I click another cell). I would like this to be immediate and without a MsgBox.
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.
I am trying to utilise a vba script for DNS resolution which i got from Followup: DNS Lookup and Ping in Excel - CodeProject
I have a list of IP addresses that I want to use this on, however if I test this by using the test instructions, excel does nothing, its just text in a cell, so I would like to know how would this procedure get called from the spreadsheet?
Code:
GetHostname("4.2.2.1") in any Excel cell.
or
Use: GetIpAddress("www.google.com") in any Excel cell.
I have unprotected cells in excel where I need to change the content (number) and cell color, but I don't want the cell borders to change. I'm a beginner with vba. I've tried protect sheet and workbook options, but looks like I need a vba code and don't know where to start.
Win 7
Office 2007
I am trying to run a macro when any cell in a range changes. I have got this to run, but only on one cell, not any of the cells in a range.
Working code:
[Code]....
Non working Code:
[Code] .........
I am at a loss as to why the range code won't work, or why the first code won't work without makig the cell reference absolute.
I am new to the whole MACRO VBA thing and I am desperately trying to learn. So this is the Situation. I recorded a macro of an If statement on a row. very simple. Some of the rows below ( Like 700) need the same formula that I recorded. I would like to highlight the cells that need the formula and run the macro on them. however the macro only runs one at a time. Is there a way that this can be avoided?
View 11 Replies View RelatedIs it possible to have a macro run as soon as there is text typed into cell A2? Basically, I have a form that needs to be filled out, but somehow, users forget to put their name in the box. So I don't want any information to be able to be typed in until a name is entered.
View 9 Replies View RelatedI have following macro run when the given cell changes:
HTML Code:
Private Sub Case_Checkout(ByVal Target As Range)
Dim KeyCells As Range
Dim WS As Worksheet
Dim LkUpVal As Range
Dim LkUpRng As Range
[Code]...
It was working...and then just stopped working. I scan a barcode into cell A1 and hit enter. Can't figure out why is stopped unhiding the tab. I haven't changed anything...
I need to check cell G5 (which is G4-G3) before running a macro. if G5 is zero i need to pop up a message to inform the user to enter data in cells G3 & G4. And if G5 is greater than Zero the macro shud run.
I have been trying to find a macro that would allow me to keep a running total in a cell by adding a number to that cell and the same cell would display the sum of the numbers entered. {i.e.} If cell a1 has 0 in it and i click on that cell and put 3 in it would display 3 if i added 2 to it it would display 5 and so on. I need it to do this for about 182 cells on one page.I found this macro that runs automatically every time and works great......But only for one cell so far..."D3"...Ugh!!! I need this macro to work for the following range of cells "colums d thru j from row 3 down to 28 in each colum. Each cell is an individual. There are 182 cells total that each need this macro to work ...all on one page
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Static dAccumulator As Double
With Target
If .Address(False, False) = "D3" Then
If Not IsEmpty(.Value) And IsNumeric(.Value) Then
dAccumulator = dAccumulator + .Value
Else
dAccumulator = 0
End If
Application.EnableEvents = False
.Value = dAccumulator
Application.EnableEvents = True
End If
End With
End Sub
As you see the macro includes cell D3 only, I have made several attempts to include more cells but have failed.... I tried like this
If .Address(False, False) = "D3,E3,F3,G3,H3,I3,J3,D4,E4...and so on" Then
I have tried ....If .Address(False, False) = "D3:J3" Then
and I have tried ....If .Address(False, False) = "23Rx7C" Then
I can make it work for cell d3 but not the others. Will I have to repeat this code for every cell?
I've a workbook with 3 tabs with names "tab1, tab2, difference" and I've a macro that calculates tab1-tab2 and puts the results in tab "difference" This macro runs every time I open the workbook or every time dataset is refreshed on any tab.
everything works great but i've following issue.
let's say my cursor is in tab2 & i refresh/update dataset on tab2 which will trigger macro to update values in tab "difference".. this is good. but, soon after macro runs & updates tab "difference", cursor is no more in tab2 but in tab "difference" this is OK if I've 1 or 2 or few tabs but i've 25 tabs & the user has to go back to the initial tab everytime dataset is updated on any tab. Is there a way for the macro to recognize where the cursor initiallly was & run the macro & place the cursor back to the initial tab?
I have written some very basic code to format a report in excel. When I run the code it take a very long time to execute and I receive the following error message at the foot of the page:
Cell (press esc to cancel)
Annoyingly I have had this error before and found the solution on the web but can't remember where. If memory serves my right I deleted some temp files from a specific location on my hard drive?
I am trying to create a menu that calls macros based on your choice using a drop down combobox (from the control toolbox). Everything works except: When i try to reset the drop down to "Main Menu" using the Linked cell $B$1 excel hangs and suts down evertime. What i would like it to do is reset the drop down to "Main Menu" each time one of the other drop downs are selected. I have really enjoyed this forum and it has been a great resource for me in the past I hope someone can help. I have attaching the file for review.
View 3 Replies View RelatedI want to be able to select a cell, for example A10, this would then run a macro that would copy and paste several cells of information along that row and paste it on another worksheet. However, if i then choose a15, a3, a79 in turn (using the control key), it would run the macro using those rows in turn.
i'm looking forward to hearing if and particuliar how this could be achieved.
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
Prevent saving and allow only through Macro
I am contracting an excel template and need to stop users saving the file using “Save” or “Save As”. I am able to do this by using the private sub below:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Saved = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True
End Sub
I also understand that by switching to “Designer” mode you can save the file and once re opened designer mode will be switched off. What I need is to allow saving using a macro, is there a code that I can insert in the macro to switch designer mode?
Is there any way to write a macro which can prevent computer illiterate users from saving their files EVERYWHERE??
View 9 Replies View RelatedI have a macro, that when running needs to display certain sheets that should be hidden again when the macro is done. I solve this by using the code
Sheets("Sheet1").Visible = True
'macro code
Sheets("Sheet1").Visible = xlVeryHidden
But what if the macro fails? Or if the user ends it by pressing Esc? Then he will be able to see the hidden sheet. Is there something I can write in the code that makes the macro hide the sheet, then show the error message?
Is there a way to prevent access to the code of a macro? I am interested in sending out some macro's, but would prefer that the user only be able to run them, and not be able to access the code via the edit button. Curiously, there have been occasion's where I wanted to edit my own work, but the edit button did not function, so I had to exit and start over to proceed. From this I'm guessing there must be a way.
View 2 Replies View RelatedI have an add-in that I'm passing around to my users, and though it has 8 or 9 subs, it only has 2 that they need to see. However, I'd rather not relegate all of the code to a single module, as there is quite a bit of it. I'm aware that I could change all the subs to functions, but they would still appear in the UDF list, and this would also be confusing to the individual responsible for the upkeep of these macros (and I'd have to rework the code a little bit).
I'm just asking if there is any direct way to do this, before I go to the trouble of making a lesser workaround. Google seems to think no, but "don't show macros in list" isn't a very solid search.
I'm using the following code to delete columns:
Dim rng As Range
Dim i As Integer, counter As Integer
Set rng = Range("1:1")
i = 1
For counter = 1 To rng.Columns.Count
'If cell i in the range contains an "x",
'delete the column
If rng.Cells(i) = "x" Then
rng.Cells(i).EntireColumn.Delete
Else
i = i + 1
End If
Next
My problem is that I have cells in other worksheets linked to the worksheet that is running this macro and everytime I run it, I get a handful of "#REF" errors. I think this problem might be solved if I could simply delete the contents of the column rather than deleting the entire column. How can I modify my code to do this?
I'd like to know if there is a change event that only occurs when a target cell is changed by the user, but does not occur when i'ts changed by a macro. nfortunately, the Worksheet_Change event occurs in both cases.
View 2 Replies View Related