Upgrading Workbook Code - Add-ins Or DLL's
May 9, 2006
Haven't been here in a long time ...
Anyway, I got a HUGE (in terms of code size) wbk that is sort of a management report writer that connects to external data bases and does lots of other stuff.
Due to the way this wbk was designed the actual reports the client requested are in it as well. So now when i need to upgrade the code i hafta use a small proc that kills all the code in the prev version of the wbk but keeps the sheets (ie reports themselves) intact, and then recreates new code modules classes etc from the upgraded version of the wbk.
Although this works for now i really want to improve the code maintenance so i want to implement dll's into this project.
The wbk itself needs to have only basic code to call dll's and some other stuff and that's it. The code itself should come in dll's into a certain folder and wait for the next upgrade . How can i do this?...
View 5 Replies
ADVERTISEMENT
Apr 1, 2009
I have some schedules set up with multiple formulas and macros (originally setup in 2003) however when attempting to make this a 2007 file when I go to copy a sheet to create another schedule I get the message “A formula or sheet you want to move or copy contains the name “xx”, which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click yes. To rename the range referred to in the formula or worksheet, click no, and enter a new name in the Name Conflict dialog box.”
View 2 Replies
View Related
Feb 18, 2010
Our office has recently upgraded from Excel 2003 to Excel 2007, and now I am having an error message with my workbook. The error is as follows:
Run-time error '9':
Subscript out of range
I believe I have all the macro security settings set up, however, I think this is a problem with a printing macro I had help with here. It was created to print the number of pages entered into a specified cell when the Print button was clicked - I think 2007 uses a slightly different button called 'Quick Print'? I'm just guessing here - but when I hit the Quick Print button, the Print Options box appears instead of immediately printing.
View 14 Replies
View Related
Feb 7, 2007
How do create a formula that will only execute this formula:
=COUNTIF($F$7:$F$106,"0")
for the rows in the range g7:g106 that are equal to 12 or 13 or 14 or 15
example
f7=0
f8=1
f9=0
f10=0
g7=12
g8=12
g9=17
g10=14
result = 2, because f7 and f10 are the only matches
View 9 Replies
View Related
Jun 23, 2014
I have multiple spreadsheets that have formulas pulling from other spreadsheets that I use for daily, monthly & annual reports. After upgrading to Windows 7 from Windows XP these spreadsheets are no longer seeing the formulas if I open multiple spreadsheets at a time. This is a problem as my monthly and annual spreadsheets need to pull from multiple workbooks in order to give me monthly and annual totals. I have macros set at "Enable all macros" and have added the file location in "Trusted Locations". What else do I need to set in order to work on multiple spreadsheets at a time and the formulas pull data as needed?
View 1 Replies
View Related
Jan 27, 2009
I use excel 2002 but some of my office are on 97, i want to add a small workbook open event code which works for me but debugs for the others?? The code is basically, go to a tab, on that tab and that range sort..
View 2 Replies
View Related
Aug 1, 2013
My company has files that are already in use. I don't know too many details about how they work, but somehow saving the file will screw it up and my boss has to go back and reset something or other to correct it. Obviously it's connected to some other software somewhere. The code below will block Save and Save-As. BUT how do I get the file to hold onto the code without actually saving the file after the code is added (since the file shouldn't be saved)?
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = False Then
Cancel = True
[Code].....
View 1 Replies
View Related
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 Replies
View Related
Jan 14, 2014
The amazing code I saw was from this thread Auto lock cells after data entry when file saved... where the code was made by Jafaar Tribak.
It basically locks cells that have had data inputted into them when the file is saved (with a nice prompt warning of the used cells being locked.
This is Jafaar's file [URL]
And this is my file where I'm trying to make it work in the "Employee data entry" Sheet:
[URL]
And finally this is the file after I tried to copy the code over : [URL]
Trying to make it so that in the employee data entry sheet locks cells after an employee inputs some data into said cells.
I thought that by setting an "inputrange" on the employee data entry sheet the code would track it down automatically but maybe I'm completely wrong. I cant see anything wrong with it (probably because I"m not that good at vba) but I fear I may have left out something really simple!
I seem to disable my other macros when i try to insert this code in (I have a macro that when you click on a cell a calendar pops up so you can click on a day from the calendar rather than typing out the date)
View 9 Replies
View Related
Sep 12, 2006
How is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 Replies
View Related
Jan 29, 2008
I have a workbook with four worksheets named
FilteredData
FilteredDataFormat
array
arrayFormat
The two sheets named FilteredDataFormat and arrayFormat exist strictly to preserve headers , and column widths.
So that for convenience sake I can clear my data without specifying a range that excludes the headers.
I simple clear the contents of the 'FilteredData' and 'array' then copy and paste 'arrayFormat' and 'FilteredDataFormat'.
Sort of an unconventional and lazy approach but I see no reason why this will not work.
Here is my original code and what happens is that excel creates another workbook for some reason unknown to me
View 10 Replies
View Related
Feb 19, 2009
This code, supplied through this forum, works perfectly in one workbook but not another. I have created a range called ColourRange, one called ColourIndex but I am getting a '400' error message when I attempt to run it. Can anone explain to me (in very simple terms) why it won't work?
Sub CopyFormatMMT()
'Colour code Owners
Dim r As Range
Dim f As Range
Dim c As Range
Dim j As Range
Set r = Range("ColourRangeMMT")
Set f = Range("ColourIndex")
Range("ColourRangeMMT").Select
Selection.Interior.ColorIndex = xlNone
For Each c In r.Cells
For Each j In f.Cells
If c = j Then
c.Interior.ColorIndex = j.Interior.ColorIndex
End If
Next j
Next c
Range("C9").Select
End Sub
View 2 Replies
View Related
Apr 7, 2009
I want to put the vba code into each "ThisWorkbook" in each workbook that is in a folder. I want the code that is placed in each "ThisWorkbook" to run whenever that workbook is opened.
How would I go about doing that? I have this code so far to open the workbooks that are in the folder.
View 6 Replies
View Related
Apr 28, 2009
I need to error check to see if a worbook is open or not. if it is not open i need it to open it. i supplied the code i am trying but it is not working. I keep all my workbooks in XLSTART.
View 2 Replies
View Related
Jul 7, 2009
I currently have the following code that allows grouping when protected and also puts the user into design mode:
View 5 Replies
View Related
Jul 11, 2009
I know this is the code to close a workbook
View 5 Replies
View Related
Nov 8, 2012
Why can't I get this code to work? The first line seems to work ok but not yet sure as the rest doen't work.
I have tried NEXT_YEAR as a string and as a number but I keep getting an error.
Sub test()
NEXT_YEAR = "2013"
ChDir "J:Maxtor backupFire DeptVacations " & NEXT_YEAR
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations " & NEXT_YEAR "VacGrp - 1 - " & NEXT_YEAR & ".xls""", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
End sub
Below is the code I am trying to replace and it works fine.
ChDir "J:Maxtor backupFire DeptVacations 2013"
Workbooks.Open(Filename:= _
"J:Maxtor backupFire DeptVacations 2013VacGrp - 1 - 2013.xls", UpdateLinks _
:=3).RunAutoMacros Which:=xlAutoOpen
View 5 Replies
View Related
Sep 29, 2013
I have two workbooks the first named "number" which just contains the number 50000 on sheet 1 cell A1. This "number" workbook is always closed and saved in c:me"number". The second named "sales" has a form control button with code as follows and is always opened and saved in c:me
Sub next invoice()
range ("A1"). Value= range ("A1"). value+1
End sub
When I press the button this code does not work because I am missing the vba code to find the close file.
View 9 Replies
View Related
Dec 31, 2013
I need a code in VBA which I have to use the Hyperlink within the workbook e.g. "Place in This Document.
Assuming I have two Worksheets WS1 = Main, and WS2 = Reference respectively
In WS1=Main, I have a list in Column from E3 to E1000. let say E3=1, E4=2, E5=3, E6=4, E7=5..., so on and so forth.
In WS2=Reference, I have a list in Column from F5 to F1002. let say F5=1, F6=2, F7=3, F8=4, F9=5..., so on and so forth.
What I want is if I click the E3=1, E4=2, E5=3, E6=4, E7=5..., in WS1=Main if will jump to specific cell in WS2=Reference e.g. F5=1, F6=2, F7=3, F8=4, F9=5...,
View 1 Replies
View Related
May 13, 2008
Does anybody know the memory limitations that Excel (2003) has with regard to the amount of vba code that a workbook can contain?
I have automated one of my spreadsheets to load a bunch of modules containing code (a few hundred thousand lines of code altogether). Before it finishes loading all of the modules I get a "Run-time error '7': Out of memory" error. When I press the Help button on the dialog I get a help page which says:
"More memory was required than is available, or a 64K segment boundary was encountered."
The help page goes on to suggest (among a few other things) that I might have a module or procedure that is too large. After reducing the sizes of individual procedures and modules, I am starting to think that there may be a limit to the absolute number of lines of code that can be stored within a single workbook.
View 9 Replies
View Related
Feb 24, 2009
I have a code that saves my worksheet once it closed. At the moment it saves the Active worksheet which is Sheet1
The problem i have is i have a few vlookup formulas that reference hidden worksheets Sheet2 & Sheet3
Would it be possible to modify this code so it saves the entire workbook?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Dim bk1 As Workbook
Dim bk As Workbook
Dim myfilename As String
myfilename = "C:files" & Range("B10") & " - " & Range("J10") & ".xls"
Set bk = ActiveWorkbook
Set bk1 = Workbooks.Add(Template:=xlWBATWorksheet)
bk1.Worksheets(1).Name = "Sheet1"
bk.Worksheets("Sheet1").Copy _
bk1.Worksheets("Sheet1")
On Error GoTo 0
bk1.SaveAs Filename:=myfilename, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWindow.Close
End Sub
View 9 Replies
View Related
May 17, 2006
is it possible to use code to save a workbook? I know this is pretty basic, but for the life of me, I cannot answer this question searching the entire website and forums.
I'm currently creating what could be called a "yearly timesheet" for a small company and trying to automate it as much as possible. I've created several buttons along with input boxes to create the data, now I would like to make two more, one to save the worksheet (preferrably using data from one cell as the workbook name) and a second to send the workbook to the local, default printer. I've come across the topics on stopping Excel from automatically asking the user to save it, etc etc but none along the lines of "this is how you create a button that if clicked, saves the workbook."
View 9 Replies
View Related
Aug 14, 2007
I want to be able to launch code in an add-in to perform a check when other workbooks are opened by the user. Code in the Open event for the add-in launches when Excel opens, not when each workbook is opened
View 2 Replies
View Related
Aug 27, 2007
I was trying to add some code through VBA to a new Workbook I'm creating. I'm following the instructions in {url}
What I need is to add an worksheet_change event procedure, and im trying it as it follows: ...
View 3 Replies
View Related
Oct 13, 2007
I am trying to paste some codes to the "ThisWorkbook" section of a file. The codes I am trying to paste will prevent the user from: doing a Save As (to keep them from saving the files anywhere else in the network) and to Disable the Macro (only showing a prompt sheet if they do so they can go back and enable the macros). The file that these codes will go to is sheet and workbook protected, and some cells are protected, and cells that they can edit have data validation formulas. In a post dated Nov 6, 2003 (thread 16750) I found a useful code that lets me paste the codes above to a target file. But when I save and close the file, the Disable macro does not work.
So I tried long method and pasted the codes into the ThisWorkbook section manually. Save and closed manually. and when I opened the file, the Disable and Enable works. Just as a background, I am a beginner with VB and I can't write my own codes yet, but I try to piece them together from forum answers. I need VB to do this because I have 263 individual files to prepare. I used to use AutoPilot, which is a scripting software but it's so prone to bonking out that I would rather do it in VB if possible. These are the codes am trying to paste:
Private Sub Workbook_BeforeSave _
(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then Cancel = True
End Sub
Private Sub Sheet_Activate()
Dim oCtrl As Office.CommandBarControl
For Each oCtrl In Application. CommandBars.FindControls(ID:=21)
oCtrl.Enabled = False
Next oCtrl
For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
oCtrl.Enabled = False
Next oCtrl
Application.CellDragAndDrop = False
End Sub...............
View 2 Replies
View Related
Mar 24, 2014
I've not had much experience with VBA code, other than recording macros and editing them a little. What i would like to do is code a macro to copy the data (text only) of a whole worksheet into a blank worksheet in a different workbook, effectively allowing the user to "upload" the data to a master workbook.
The user will complete data entry into a daily template worksheet containing all formulae to obtain necessary daily data. I would like them to then be able to click a button that runs a macro copying the text data from the daily template to a monthly workbook. I have no problem recording this in a macro and the code for that (no doubt there is a more efficient way) is:
[Code] .....
The monthly workbook will contain 31 sheets (named 1,2,3...31) for the days, and each month will have it's own workbook (named Jan, Feb, Mar...Dec).
The key thing I need is using cells within the daily workbook to determine the month and day used by VBA for the filename and worksheet respectively.
Cell B5 contains the day (eg. 1) and C5 the month (e.g. Jan)
View 8 Replies
View Related
Jun 12, 2014
Normally when a code copies a cell value from Workbook-A, closes Workbook-A and then pastes the value into Workbook-B it works flawlessly. However, i noticed that this doesn't work in all case, for example when a code repeats itself with the "For - Next" function then when the code copies from one workbook to the other it causes an error.
Here is my code and i made the part of the code that is red is where the issue lies.
[Code] .....
View 14 Replies
View Related
Jun 13, 2014
Here is the file rCell.xlsm, in order for the code to execute you need to select the green cell. When you hit the macro a copy of the workbook is placed on your desktop. The code then tries to copy a range from the copied workbook and paste it in the original macro workbook. The pasting part is crashing. In the code i marked the issue in red.
View 1 Replies
View Related
Jul 15, 2014
I want to put a macro in that will refresh the workbook before this next part of code is run:
[Code] .....
I tried putting this in before:
[Code] ....
But it isn't refreshing the workbook before the next part is run
View 2 Replies
View Related
Jul 16, 2014
I have a code that opens another WB, and gather data from it. most of the lines look like this:
HTML Code:Â
wb.Sheets("Data").Range("K" & Rows.Count).End(xlUp).Offset(1) = wb2.Sheets(1).Range("M49").Value
However i need it to gather the sum: M49+M50, how can i do this without importing both values and then adding them in a 3rd column, like i am currently doing.
View 3 Replies
View Related
