I am setting up VBA code to create different excel workbooks. One of the differences is in the VBA Tools References that must be enabled to make the whole thing function.
How do I write VBA code to turn on and off specific tool references ie. if i had a reference called OrbitCOM Library how can I turn it on and off using VBA code.
VBA code to extract excel report from tools e.g. SAP or other tools.
View 3 Replies View RelatedIs there code available that will hide the Tools Menu within excel?
View 5 Replies View RelatedI am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
I have a spreadsheet that queries another spreadsheet, using MSQuery. The query is of a named range on another workbook. What I am trying to do is change the workbook reference, to another similar workbook, with the same range name. MSQuery is not easily letting me do this. I figured there was a way to simply change the file that the created table (in MSQuery) is using. There doesn't seem to be a way to do this.
The named range is "data." This is what the SQL view looks like: ...
I would like to reference cells in another tab in my worksheet, but change the orientation.
For example, in Tab 2, I want to make rows A1:A26 equal to columns A1:Z1. Do I have to click back and forth between worksheets 26 times, or is there a way around this?
I have a large excel matrix that contains many references to a Word document. The path to that document includes /ref Doc1/ in every reference. I want to change that to /ref Doc1 rev1/. Is there a 'replace' tool to do that?
View 2 Replies View RelatedIs there a way of getting the cell references in a formula change following a change in the content of a cell in the worksheet?
View 9 Replies View RelatedI have many files that has a filepath references in the cell values, for example:
='\serverfolder1subfolder2[Filename.xls]Sheet'!$B$82
Is there a way to change all the references to direct to another place?
Filenames don't change, only the '\serverfolder1subfolder2 path
This issue is similar to absolute/relative references, but it's related to the data being referenced, not the cells doing the referencing. If I have a call the references cell A3 then I insert a row above row 3 (making the original A3 cell now A4), my reference automatically changes to A4. How can I get it to stay at A3 no matter what happens?
This may sound like an odd request, but I reference a bunch of cells on a worksheet. This sheet retrieves data from a database which changes regularly. The changes that it receives are not just updated in the cells, but rather the rows are deleted and reinserted changing all my references to this sheet. For example, say I import data and it fills cells A1 to A20 and I make a reference to A15. If I change the data that is imported and the data now only fills cells A1 to A10, the reference to A15 is now set to #REF since it actually clears all cells from A1 to A20 and then reinserts new data.
How to change from absolute references to relative references.
Example :
ws.Range("G" & NextRow).Formula = "=" & Range("H" & NextRow).Address & "+" & ws.Range("I" & NextRow).Address
This code return the absolute references---> =$H$365+$I$365
, and i want change to relative references, like this ---> =H365+I365
I have an excel add-in that has got few macros which require two references,
1) Microsoft Word 12.0 object library
2) Microsoft Internet Controls
I have checked both these references and the macros are running perfectly on my PC. but when I distribute this add-in, sometimes the references are checked but sometimes it gives an error and the user is required to check the reference again.
Can i use some code to automatically check these two references. or is there some other easier way to get rid of this error.
I am modifying some vba code for an Excel workbook. The code references the row 35 a lot (altogether 16 times).
Example references:
With Worksheets("YTDBudget").Range("c35:c200")
For i = 35 To 999
Range("e35:h" & i).Select
Worksheets("Budget").Range("A35:D200").Copy _
Destination:=Worksheets("YTDBUDget").Range("a35")
Range("e35").Select
Rows("35:35").Select
Range("A35").ClearContents
Currently the "special row 35" is identical for a couple of sheets. Due to the nature of the changes I need to make there
is a good chance that "special row" will be different for each sheet (i.e. stay at 35 for one sheet but be 36 in the other).
I am thinking of creating a couple of public constants (Sheet1DataBegins and Sheet2DataBegins) that I can then use instead of the hardcoded 35. This presents problems in the I would have to string together the cell references like e35:
CellE = "e" & Sheet1DataBegins
Range(CellE).Select
Which looks kinda ugly to me.
This also has the problem with the column being hardcoded ... but that's another problem that I am not ready to tackle ... The original coding is not mine and I am just tasked with making a modification
Is there a way to absolute reference multiple cells at one time?
View 5 Replies View RelatedIf I have a cell containing a formula say c1= sum(a1..a10) and the output appears in a50. how do I colour code it so I know the formula in c1 is linked to the output in a50?
View 5 Replies View RelatedI would like to know whether its possible to fix a sheet to always be the second sheet in a work book no matter how many other sheets are added. Currently I have set up macros to add subsequent sheets before the last sheet, so that Sheets("Number 2") remain sheets(2). Is there some way I can lock the first two sheets so that when I reference them in my code as sheets(1) and sheets(2) it will reference the right sheets. Right now I have it set up so that Sheets("Number 2") can be renamed by the user, and data inputted on this sheet, but I require that the user not be able to move this sheet and the sheet preceding it.
View 5 Replies View RelatedNeed to create Working order (in Excel 2007) for filling machine in my factory. Working order is changeable from day to day and the deference is product quantity. Product quantity in working order [File - Working order] is changing according to the each sales plan [7-Jan-13, 8-Jan-13] - (File Working order is Linked to the appropriate sales plan in this case - [7-Jan-13]).
Anyway, I need to change the source workbook "sales plan" from [7-Jan-13] to [8-Jan-13] by changing source workbook name in red cell [File - Working order] and not to browsing location?
All source workbooks [sales plan 7-Jan-13, 8-Jan-13]are at the same location in the same folder, only difference is the source workbook names. So, is it possible to change source workbook name (File - Working order - red cell) for all external references (File - Working order - green cells) at the same time, and avoid annoying browsing?
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
I'm "missing something" here. I used the EOMONTH function in a recently developed workbook. In order to activate that function, I had to go to the Tools menu, select the Add Ins option and add the Analysis Toolpak to my Excel configuration ... no problem, worked like a charm. Then I sent the workbook to another user. Of course, the EOMONTH function came up with errors when she opened it. I talked her through the Tools/AddIns/Analysis Tookpak configuration change ... but could not get the EOMONTH formula to work.
View 5 Replies View RelatedJust discovered the drawing tools on my quick access tool bar are disabled. All were enabled and working fine just yesterday. I'm running 07 on xp. I've shut down and rebooted, no change. Need to know how this could have happened but mostly, how to enable.
I use several different workbooks during the day, and sometimes I would prefer the " move selection after enter direction" option to = "Right" and sometimes "Down". It doesn't seem to be an option that is saved with the workbook.
what could be done to a workbooks code to change this option upon opening the book?
I placed an add-in (.xla file) with a " function" and a "sub" into C:Microsoft Office XPOffice10Library. When I go to Tools-->Add-Ins the Add-In doesn't show up. When I browse the file it says it already exists and still doesn't show up. The strange thing is that the "function" does work, but the sub (which is a sub worksheet_change) doesn't run. I also tried doing the steps as they are laid out in http://www.ozgrid.com/VBA/excel-add-in-create.htm and ended up with the same problem. The function works when I open up a brand new Excel application and use Book1, so I know the function isn't embedded in the sheet.
View 4 Replies View RelatedI have installed Excel 2007 but cannot find the tools menu [or tab]? I see tabs like 'Home', 'Insert', 'Formulas', 'Data', etc. but no tools?
View 2 Replies View RelatedWhy when I open some files that have macros and I enable macros on initial opening and then go to tools-macro-macros it's not showing anything there, but when I press Alt F11 then I can see macros?
Is there any way to change this that I can see all macros from tools-macro-macros?
I've created and excel spreadsheet that has code like this in a Module named Print w/ in the workbook:
Sub Print_wsPrint()
Sheets("Print").PrintOut
End Sub
I have saved it as an MS Office Excel add-in.
I added the add-in to my excel environment via Tools, Add ins...and let it copy it to my C:Documents and SettingsjohnApplication DataMicrosoftAddIns directory.
But yet when I create a menu button and assign the Print_wsPrint to a custom menu, it complains and says "The macro Model.xls!Print_wsPrint cannot be found," where Model.xls is the workbook I am in. It's in my add-in, not in the current wb.
When I choose tools -----> options from the menu bar nothing happen I can not changes my options. I am using excel 2000, I must of changes something but I don't not remember what. Can someone help turn back this option
View 9 Replies View RelatedI have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water
SW = Saline eater
CW = connate water
MW = Meteoric water
numeric code as follow
FW=1
SW=2
CW=3
MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
I want to hide and unhide tool / macros menu so that users don't run the macros in the workbooks.