VBA Runs In One Worksheet But Not Another
Dec 31, 2013
I wrote this VBA for a particular sheet:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("H5") = "Y" Then
Columns("L").EntireColumn.Hidden = True
Else
Columns("L").EntireColumn.Hidden = False
End If
End Sub
If the value of H5 is "Y" it hides column "L". It was working fine at one time. Then I wrote a bunch of UDF's at the workbook module level. I then went back it use the "hide" function and it didn't work.
I created a couple new worksheets in the same workbook, and the same VBA works fine on them. I created a copy of the "non-working" worksheet and added the VBA to the copied sheet and it fails to work.
If I create an error in the VBA (like: Column("L").EntireColumn.Hidden = True), I get an error when I change the (non-working) worksheet - so I know it is "trying to run", at least sort of.
View 6 Replies
ADVERTISEMENT
Oct 26, 2011
I have a file that needs to be locked down so that the end user does not have the ability to enter data where they shouldn't. The end users are very Excel illiterate, and have proven that I need to have high security in place. Here is my current VBA code - the purpose of which is to hide/unhide rows depending on data entries. (Disclaimer: this code may not be pretty as I am a VBA rookie, and I've begged borrowed and stolen from about 10 other threads on this board to get where I am, and yes it works).
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "C7" Then Call Changeto1
If Not Intersect(Range("c9"), Target) Is Nothing Then
Rows("15:44").EntireRow.Hidden = True
Rows("15:" & Range("c9").Value + 14).EntireRow.Hidden = False
End If
[Code]...
The question is now....how can I unprotect the sheet so that this code works without allowing them to enter data where they shouldn't. I've been at this for days already.
View 7 Replies
View Related
Dec 3, 2008
When I step through a macro (sessions) or run the macro seperately, it works as intended. When I have it run off of a worksheet_change it doesn't work. Is there an easy fix for this?
Original
Sub sessions()
Dim a As Integer
Dim x As Long
x = Sheets(1).Range("a" & Rows.Count).End(xlUp).Row
Sheets(1).Unprotect Password:="password"
For a = 2 To x
If Cells(a, "m") = Cells(a, "d") And Cells(a, "m") 0 Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = True
ElseIf Cells(a, "m") Cells(a, "d") Then
Cells(a, "f").Resize(1, 6).Select
Selection.Locked = False
End If
Next a
View 9 Replies
View Related
Jun 9, 2009
I have written a number of UDFs, which work fine in my spreadsheet. Two of these are more complex, and I'm having a few problems...
When I run any VBA routine (i.e. control passed to VB), these two UDFs return #VALUE! errors, but I can't see why. As these are Functions, I can't think of a way to test where the error is being generated, like I would in a Sub
When I return to Excel, any recalculation fixes the problem, which makes me think something is declared incorrectly. All named cell references relate to other VBA functions, calling the relevant row / column numbers, and I think these are all correct. I've tried removing AS INTEGER etc, to avoid type mismatches, and adding APPLICATION in front of worksheetfunction...
Function precedentStart(Target As Range) As Integer
Application.Volatile
With ThisWorkbook.Sheets("sheet1")
Dim splitPrecedent As Variant, lookupI As Integer, rowI As Integer
precedentStart = .Cells(Target.Row, colOrigStart).Value
splitPrecedent = Split(Target.Text, ",")
For i = LBound(splitPrecedent) To UBound(splitPrecedent)
rowI = WorksheetFunction.Match(splitPrecedent(i), .Range("schedRefs"), 0)
lookupI = .Cells(rowI, colAdjEnd).Value
If lookupI > precedentStart Then precedentStart = lookupI
Next i
End With
End Function
View 9 Replies
View Related
Nov 28, 2009
I can't seem to find an answer for this, but as an alternative, on Worksheet Activate the code runs and places a 1 in Range("A1") and therefore the next time the macro will not run.
This works fine for me, but just thinking if somebody who doesn't know why the one is in A1 and deletes it this macro will run again on sheet activate.
Private Sub Worksheet_Activate()
Dim LR As Long
Dim rng As Range
If Sheets("Charts").Range("A1").Value = 1 Then Exit Sub
LR = Sheets("POD").Cells(Rows.Count, "B").End(xlUp).Row
With Sheets("Charts")
.Range("I129").FormulaR1C1 = "=ROWS(R1C1:R[-128]C[-8])"
Set rng = .Range("A129").Resize(, 10)
rng.Copy rng.Resize(LR - 4)...............
View 9 Replies
View Related
Dec 12, 2008
I created this spreadsheet to assist with pricing for my towing company. I struggled with getting the dropdown lists and combo boxes to work, and I created and deleted many of them before I got it to work like I wanted, but I think I left a mess. It runs slow, showing a delay when I click on different radio buttons, and there is an exclamation point in the Excel icon in the file directory. Could someone take a look in the code structure and tell me if there are any opportunities to clean it up. RateCalcApp.xlsm
View 2 Replies
View Related
Apr 3, 2009
In VBA I use this command, which runs *.vbs file that is used as a message box:
View 3 Replies
View Related
Jun 10, 2014
I have a macro which filters data on one sheet and copies that data onto 34 new sheets . This works perfectly until the 25th pass when Excel advises that there is insufficient memory to continue. I assumed that this was connected to the clipboard and its ability to remember 24 chunks of data so I turned it off but still Excel gave up at the 25th pass. Is there a way that I can clear the clipboard during the macro's execution or is there something else going on?
View 7 Replies
View Related
Dec 23, 2006
why the On Error GoTo statement only executes the first time an error occurs in the following
On Error GoTo CubeNotFound
For x = 2 To TotalRecords
Cells.Find(What:=WSID, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
'Other actions are located here, but have been removed. These actions take place if the Find is succesful.
CubeNotFound:
Message = MsgBox("WS ID " & WSID & " was not found! Excel will continue with the next WS ID.", vbOKOnly, "WS ID NOT FOUND")
Next x
I have that statement there because if WS ID is not found then VB generates an error that the user won't be able to interpret, and also stops the code. I want VB to continue to the next record if one is not found.
View 9 Replies
View Related
Feb 5, 2008
Running Windows XP; Excel 2003
I have a fairly simply macro.
It copies certain sheets out of a workbook (for a new workbook)
Unprotect the sheets in the new workbook
Deletes a particular cell on each sheet
Unhides a sheet
Sets all of the new worksheets to normal view
Protects the new sheets
Lets the user save and name the new workbook.
All is working fine Except it will not change the new workbook from Page Break View to Normal view.
The code I'm using = Sheets("name").Activate
Activewindow.View=xlNormal
Here's the strangest part. When I run the macro from a command button, the newly created workbook retains the page break view (as is in the original).
When I run the macro line by line via F8, IT WORKS JUST FINE
View 9 Replies
View Related
Feb 15, 2008
I have created a macro in xl2000 which was working fine - although through corporate upgrades my machine has now been upgraded with xl2003 and the macro runs for a bit and then crashes on the code below, although it only highlights teh 'rnum'
rnum = ActiveCell.Row
Is there a difference between xl2000 & xl2003?
View 9 Replies
View Related
Nov 13, 2008
The code below runs on a spreadsheet that has approx 600 rows which INDEX and MATCH another spreadsheet which has approx 600 rows. I takes about 4 mins to run.
Sub QC()
Range("T1").Select
ActiveCell.FormulaR1C1 = "Compared CCCD"
Range("V4").Select
Columns("T:T").ColumnWidth = 15.29
Range("S1").Select
Selection.Copy
Range("T1").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Columns("T:T").Select...................
View 9 Replies
View Related
Jun 30, 2009
I want to display a progress bar for when those macros are run. I've read many different ways to do this when I search it on google but I would prefer to use the cleanest method where I can simply call the appropriate procedure for each different macro, without changing any of the other code of the progress bar.
View 9 Replies
View Related
Aug 12, 2009
I wrote some code that runs with no problem on my computer as well as another coworkers computer, but will not work on a 2nd coworker's computer. The "subscript out of range" error shows up at the point when the EMInput worksheet in the EMInput workbook is copied to the new workbook called "Out of Plane Section Cuts" (SEE BELOW). Is there an option in EXCEL or somewhere that could affect this. I don't see why you would get a subscript out of range error since the EMInput workbook is open....
View 9 Replies
View Related
Feb 16, 2007
I've a problem with the below code.
Option Explicit
Private Sub Workbook_Open()
Dim x As Date
x = InputBox("Enter End Date!")
Range("B2") = x
With Application. CommandBars("File")
.Controls("Save").Enabled = False
.Controls("Save").Visible = False
.Controls("Save As...").Enabled = False
.Controls("Save As...").Visible = False
End With
With Application.CommandBars("Standard")
.Controls("Save").Enabled = False .................
View 9 Replies
View Related
Aug 4, 2007
I have an Excel workbook which runs a query via DAO against an Access database. The query usually takes about 6-7 seconds. Id like to do some animation while the query is running. My animation is just a simple Autoshape that I rotate (1 degree at a time) in a loop. Is there a way I can excecute my query and then continue to run my code while the query is excecuting?
View 2 Replies
View Related
Mar 10, 2013
Website has a URL where the last element is a number within a range (e.g. 1000 - 4000).
But the numbers are not sequential (e.g there are 100 pages, first URL ends "1001", last one ends "6000".
As shown below I set the code to cycle through every possible number:
VB:
For i = 1001 To 6000
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://(whatever)" & i _
, Destination:="(Whichever cell the output starts in)"
Not the most constructive use of the national grid.
Is there a VBA that can check whether the URL "number" is genuine before doing anything else - and move straight to the next "i" if it doesn't exist?
View 2 Replies
View Related
Feb 6, 2014
When running the following code for NSLookup in a module within the current workbook, it runs fine but when I put it in a XLAM file and try and use it as an addin I only get #NAME? as a result. I believe that that addin is installed properly because I have a second module with a sub routine in it (GetIPStatus) that is working, and when entering the command =NSLookup(A2) in a cell it shows up properly like it's an available function, but it does not seem to execute. Do I have to do anything special for a function to run as an addin?
View 4 Replies
View Related
Aug 18, 2014
I have set double click event to open my userform.
It opens normally, but when I try to close It, It opens again. After that I can close It normally.
I checked Userform code, but can't figure out why event triggers twice. What's wrong ??
This is my double click code :
[Code] ......
View 1 Replies
View Related
Jan 28, 2014
The code below doesn't run if the workbook that is saves to is all ready open. is there a way for the macro to run if the workbook is closed or open?
[Code] ......
As with bigger files i will open the folder before i run the macro.
View 6 Replies
View Related
Dec 10, 2008
I have an annoying issue that occurs after running a reasonably long script involving a number of spreadsheets. The script all runs fine and leaves 4 output spreadsheets open for final manual formatting. However the mouse focus is changed by the script (somehow?) and no matter what I do the mouse wants to multi select cells. Clicking in a single cell always selects at least 3 or 4 cells and sometimes the whole column.
The only place in the code where I set any rules realted to selection is to set a file dialog to allow multi selection of files. That is
View 5 Replies
View Related
Mar 11, 2009
I have a macro which reads data from a large number (up to 500) of books in a folder. This extracted information is subject to some fairly heavy analysis which means that the macro runs very slowly as the sheet is constantly calculating.
I would like to add some code - maybe after 'screen updating = false' - that would turn off auto calculation while the macro runs and then restore it once the macro is finished.
View 3 Replies
View Related
Jun 30, 2009
i have a button on my workbook1 that opens another workbook (lets call it workbook2) and automatically runs a macro on opening it. Once opened i have a button on workbook2 to close itself. I then go back to see workbook1. This all works but then when i click on the button to open the workbook2 again it opens but the macro does not run.
View 4 Replies
View Related
Jul 20, 2009
Is it possible to have a macro which effectively runs all the time.
For example, if the user imputs something which is wrong I want the macro to pick it up straight away with a message box.
View 13 Replies
View Related
Sep 24, 2009
I searched for this and found a few examples on splash screens, but if someone could help I would appreciate it.
Basically I have this massive macro that runs, I recorded some of it, and VBA the rest. It does a bunch of things like bringing in different spreadsheets, formatting vlookups blah blah blah... Anyway for a nice touch I was hoping that I could get a splash screen or something that will run while this macro is running in the background. How hard would this be? The macro is button driven...
View 9 Replies
View Related
Nov 30, 2009
Those "False, False, False, False" parameters to run a histogram, whatever do they mean? Application.Run "ATPVBAEN.XLAM!Histogram", Range("Inputs"), Hist.Offset(0, 3), Worksheets("OutputSheet").Range("Outputs"), False, False, False, False
Through recorded code, I've seen that to create a histogram, the third False is a "True"... but let's say I've already got the blank histogram created. I'm running a simulation (where the inputs range is being updated with every run), and I want the histogram to be filled in at the end of all the runs. What do these parameters have to do with it? Having them all as false, like I've been told, isn't working.
View 4 Replies
View Related
Dec 17, 2009
I have a macro in my worksheet that runs as a change event on certain comment cells. This macro unprotects the worksheet, expands the merged comment cell that just change, and re-protects the worksheet. The cell expansion works great. When I re-protect the worksheet all of the cells work correctly, except the cell that was just changed. Any other "unlocked" cell in the document stays unlocked after the protection is applied, but the cell that just changed becomes locked. I need this cell to remain unlocked to allow the user to update the comments. Here is my full
View 3 Replies
View Related
Mar 11, 2013
Currently i have a button on sheet 1, that contains this VBA code
Code:
ActiveWorkbook.RefreshAll
Sheets 2 , 3 ,5 all have a MS Queries.
However I only need sheets 2 and 3 refreshed , and not 5.
At the moment it takes long to refresh all , and want to specify which sheets need to be refreshed, to make it faster for the user.
FYG, I cannot remove sheet 5, as it is required on adhoc basis.
View 2 Replies
View Related
Jan 11, 2007
I have a spreadsheet that is made up of single line items with sub-totals at various points going down the sheet (ie the spreadsheet is split into sections)
Each section is about 100 rows long and 40 columns wide, 10 of which are sub-totals, all using the same formula from that i enter in column A.
As i add new sections i have recorded a simple macro that copies the active cell (ie A15 for example). It then pastes into D15, F15, H15, N15, X15 and then returns to A15.
I have linked this up to ctrl X for ease of running.
I have around 500 section totals to "ctrl X", per spreadsheet and 10 spreadsheets to do in total. I have firstly filtered all the line items (rows) out to leave me just the sub-totals to [ctrl x] [arrow down] [ctrl x] [arrow down] [ctrl x] etc etc etc....
Is there anyway to automate this process, i have tried recording another macro of me [ctrl x] [arrow down] about 30 times, but when i run it, it pastes on the hidden rows, not just on what you see on the screen. Is there a bit of code i can enter so it only sees what is visible.??
View 9 Replies
View Related
May 19, 2008
I have a public array in a module in my spreadsheet. The array is an array of a class type (not integers, strings, etc). When I run the macro that uses the array for processing the first time, everything seems to work properly. However, if I run the macro a second time WITHOUT getting out of Excel and coming back in, it doesn't come back with the proper results. It seems like either the array isn't being cleared out properly between the macro runs, OR that something in the macro is overwriting memory. Is there a way to make sure the array is being cleared out between runs of the macro?
View 9 Replies
View Related
