how can I detect the OS of the computer a sheet is running on and launch a macro accordingly?
I have built a calculator that utilizes an embedded wave file but doesn't work correctly with Vista. With Vista it actually causes WMP to be launched instead of just playing the file. So I was hoping to be able to skip the sound on Vista machines but play for all other OS's.
I'm putting together a workbook which tracks operating hours for 17 pieces of equipment (units) at 7 job sites.
I need to track and record the number of hours where at least two of units 1-6 operate simultaneously each month.
So far I have only succeeded in creating another table which pulls all records for units 1-6 from the Log. The table can then be sorted by date (for some reason, this sorting only kind of works) so simultaneous operating hours can more easily identified and summed manually.
I'll attach a workbook with mock data which shows what I have so far. The complete workbook also does some other calculations which I have removed for clarity. That is why there is some "extra" info in the workbook.
The workbook is set-up where:
LOG: This is where the user records when each unit was operated.
LOG2: This is where I've attempted to isolate records for units 1-6 and sum simultaneous operating hours.
Factors+Limits: This is where the fields for the lists are stored as well as other factors used throughout the workbook.
Monthly: I'd like to be able to records simultaneous operating hours here.
If I have an application developed in Excel 2000 with Vista Operating System, does it matter what operating system (XP or later) under which it runs.
View 2 Replies View RelatedAs per the attached sheet Count If is not operating.
Effective Reading Capability
Effective Reading Capability
Effecting Writing Capability
Application of Basic Mathematics 0.4
Effective Listening Skills 0.3
Effective Speaking Capability 0.3
Attitude to Cleanliness 0.3
Idea Generation & Creative thinking 0.1
Decision Making Skills ( Upto the mandate)0.2
[code].....
here If I say Countif(B4:B20, >=0.5) But it shows Zero.
I need to create a macro that inserts a row on a selected cell on a worksheet then performs the following function on 5 different worksheets. The worksheet names are "MD Forecast", "ED Forecast", "MB Forecast", "PW Forecast", and "RV Forecast".
The code I need ran on the worksheets is:
Formula:
[Code] .....
If possible, the row that is initially inserted would be the row that the code needs to be performed on. The function of the code above is to copy everything on the inserted row and 300 rows below, paste one row below the new row, and finally clear the contents of the inserted row. The column range for this to be done to is I:BH.
I am currently working on doing some operating lease schedule. I send the info to the field and people fill out the information such as the term of the lease and payment. Then I need to review the terms of the lease and if the term is after 8/31/09 then the lease is classified as long term thus I need to compute the payments until the lease expires. Currently this process is done manually and people have to go and calculate payment by payment which just makes it hard for me to review all the spreadsheets that we get. In the past this process takes about three days to review but I know there is a faster way.
I am attaching what I have created and I put some comments in the worksheet to better explain.
I am trying to set up an IF formula for real estate operating costs. I am guessing that this is going to work best with an IF statement.
(Sample of the spreadsheet is attached)
I know this is wrong but it expresses what I am trying to do.
=IF(C5="Gross",B10=following year of B9 with month/Day being 1/1), if not B10=the folling year of B9 with same month/day)
1Cell C5 can either be Gross or Net from a drop down list.
2Cell B9 lists 3/1/2008
3If cell C5 is gross, cell B10 should be the following year from cell B9 but with starting with 1/1/??? As the month and day(year start).
B11 would then be the following year from B10
(following month/day pattern).
4If Cell C5 is NET, cell B10 should be the following year from cell B9 using the same month and day 3/1/????
I need to send a file to hundreds of users so that I can tell who has a particular add-in installed. This file will then be saved to a particular directory. The problem I'm having is controlling the error that occurs when the add-in is NOT installed. The below approach (using On Error Goto) clearly isn't the way to go. I need something like
If(Iserror(AddIns("AmartaSpecs") Then Cells(2,3)="No"
The code I have so far is:
Sub Amarta_Specs()
Dim a
'ActiveSheet.Shapes("Button 1").Delete
Cells(1, 1) = "Username"
Cells(1, 2) = "Computer Name"
Cells(1, 3) = "Add-in installed?"
Cells(2, 1) = Environ("Username")
Cells(2, 2) = Environ("Computername")
On Error GoTo Error
Set a = AddIns("AmartaSpecs")
Cells(2, 3) = "Yes"
Error:
Cells(2, 3) = "No"
ChDir "E:FinanceSQL Add-in test"
ActiveWorkbook.SaveAs Filename:="E:FinanceSQL Add-in test" & Environ("Username") & ".xls"
End Sub
I am trying to create a code in the Worksheet_change event. I have hundreds of rows of data in a sheet. Each row of data is one unique project. What I am trying to do is figure out if any cell in a specific row has changed, and place a date at the end of that row idicating the date of the change.
View 9 Replies View RelatedHow to detect msgbox pup up event.
Does any function like msgbox.activate=true or somrthing can used.
I want to avoid msgbox show up and close msgbox automaticly.
I know
application.displayalters=False
But it's seem not to apply to msgbox close.
I searched this site and found the code below to check if a worksheet to be updated is already open. It works fine (gets the desired message) if the workbook is open, but if not open I get "Run-time Error 9, Subscript out of range". I've played with it for a couple hours,
VB:
Dim wBook As Workbook
'Check that Summary worksheet is available for update.
Set wBook = Workbooks("CommittedSummary.xls")
If wBook Is Nothing Then 'Not open
'Do nothing
Else 'It is open
i = MsgBox("The 'CommittedSummary.xls' workbook is already in use and
cannot be updated. Please try again when the workbook Is available", _
vbCritical, "Committed Request Edit")
Goto EndUpdate
End If
I have a column with employee id's. Sometimes two or more employee ID's are double. I need to dedect if there are ID numbers that appears more then ones. If I found such a number I can copy that.
Has someone an idea or is there a build in function to handle this?
How can I detect on an excel workbook which cell have links(formula =) to
other workbooks without have to put my cursor on top of each linked cell.
I have had a hard time finding this cells. I can see the links by choosing Edit
then links, and excel will display list of links, but it does not specify
which cells contain those links
I am doing a bit of data spring cleaning... I need some VBA that will look at column FT for duplicates. I want it then to keep only the most recent one, so it would delete the others rows completely. Column B contains a date and time. When removing the items it should make a copy of them to the sheet Delete Log.
View 1 Replies View RelatedI have a workbook with a few worksheets. In sheet1 I have a few ranges which are linked to different parts in the other excel sheets.
Is there anyway to check if a range is being used as a link in another sheet.
Eg.
In sheet1 , A1 i have a value Apple.
In sheet2, A1 I have linked it to sheet1 A1.
is it possible to detect the link in sheet2 A1 from Sheet1 A1? I tried using Trace dependents but all it shows me is the link symbol? An arrow with a small excel sheet.
Is there anyway to detect if the user has just printed. I have code which transfer data from the sheet to an access file when the user prints using the button I placed on the sheet, unfortunately some users are using the print button on the toolbar.
View 9 Replies View RelatedIs there some way to detect that a key (specific or any) is being pressed when an Excel workbook is being opened to activate optional precessing in the macro code?
View 9 Replies View Relatedif i have data across multiple rows than is it possible to analyze that data in say row 1 and once row 1 ends move to row 2. example
Array Data
Colmn A B C D E
Row1: 3 1 4 6 8
Row2: 6 7 8 9 10
Row3: 4 5 1 2 8
Row4: 6 4 3 2 1
Row5: 2 4 1 2 2
OutPut Format
Colmn F
Row 1: Age is 3
Row 2: Always Same - Note this appears even though not part of data
Row 3: Year is 1
Row 4: Time is 4
Row 5: Hour is 6
Row 6: Minute is 8
Row 7: !
!Not Part of Output Comment: Now row 1 data has ended. Move to Row 2!
Row 8: Age is 6
Row 9: Always Same - Note this appears even though not part of data
Row 10: Year is 7
Row 11: Time is 8
Row 12: Hour is 9
Row 13: Minute is 10
!
etc
etc
etc
also note that the text that comes up in each row is not part of the data portion. Its just the number or string that gets pulled from the data portion.
I'm trying to automate a spreadsheet that downloads a set of tickers from Bloomberg and then fills across a range with a series of data. I have most of the VBA code I need, but my problem is that on opening the spreadsheet, the code moves on to fill the range before all the tickers have downloaded correctly.
I've tried using application.wait and setting up loops until the time reaches a certain value, but these seem to stop Excel updating the list of tickers (presumably the loops stop excel gathering the data?). I suspect that what I need to use is something like the querytable. afterrefresh, but am not sure I'm using it correctly since my knowledge of VBA is very rudimentary.
(i) is there a way of getting the sheet to open, and then just wait for a period of time while still allowing the tickers to download, and/or
(ii) any way to detect when the tickers have finished downloading before the next step of code can commence?
How can I with vba detect when someone changes the color of the font in a cell
View 5 Replies View RelatedI am trying to detect if a user leaves a cell empty. I have tried If statements like:
If Target.Value = "" Then
and
If (IsNull(Target.Value) Then
The problem is when the user uses the spacebar to delete an entry in that cell. If they leave any spaces after exiting the cell those if statements fail to run.
In column A i have a range of dates
In column B i have a range of descriptions
In column C i have formula which calculates an interest figure based on the date entered in column A and a date entered in a cell elsewhere. It also makes the cell display a couple of different strings of text if conditions are not met e.g. It displays "too early" if the date entered is more than 4 years before the date entered in the cell elsewhere
The problem that im having is this:
When i try to calculate column C i want it to throw an error if there is anything but a numeric value in the column. But it is obviously considering the cells in column C with for e.g. "too early" as a "0". I have tried all sorts of formula to do this to no avail, IFTEXT, IFNUMBER, ISTEXT, ISNONTEXT etc etc.
I need to detect blank rows and then delete them but for the formula not be be affected
In the example attached there are 4 components but i may only need to use 3 so row 4 would be blank
i need to find the blank rows and delete them and for the formula in cells G12:H14 to be update as necessary
at the moment if i delete the rows i get a REF# in place of the cell which has been deleted
i have tried this on a simple formula and when you delete a row the formula changes as required
I am currently using the following macro to input a formula into my worksheet:
View 12 Replies View RelatedI will have a cell that has company names in it and I need to parse each name an then do some work with them but when I started this project I was told each company will be seperated by some number of spaces.
Ex.) |Company1(534553) Company2(3544)|
Right now this is how I'm parsing a line like this:
Afternoon All - i have an excel workbook that runs on 20 remote machines that i do not have direct access to - i am placing upgrades on the net but looking at finding a way for excel to detect the update what i need it to do first is check if the machine is connected to the internet.
View 6 Replies View RelatedI often have Excel sheets in which Excel tells me there are hyperlinks and content from other sources. I guess it happens because I copy something from one sheet to another.
First, is it possible to copy-and-paste from a sheet to another and only copy the concrete formulas and formats, and no source to the old file?
Secondly, how can I detect where these hyperlinks and links to other sources are? I see they exists, but I can't seem to find them. Does Excel have such function? Can I detect them with a VBA script?
let's say combobox1 has a list of:
apple (KG)
apple (PKT)
apple (BAG)
orange (KG)
Orange (PKT)
Orange (BAG)
and a command button.
Can I make it in a way that when command button detects (kg) in combox1, the caption of commandButton will change to "KG"? if detect (BAG) combobox1, then commandButton is "Bag"?
We have a workbook with over 20 worksheets, each sheet with one or more tables in it. Several people use the workbook and we have decided that when someone changes a cell, they should mark the changed cell by filling it with blue using Format Cell. We have then nominated one person at end of each week who will check all the changes made during the week and either approve or reject them. So her task is to find all the blue cells, check them and then take the shading off.
What I would like to do is to create a macro to help her. I would like the macro to automatically identify all the blue cells in the workbook and give her a list of these cells. I wonder if someone could give me direction on a couple of things:
1) The command in the macro that would return whether a cell is shaded blue
2) The way in which I could cycle through all the content of each worksheet. I'm thinking worse case I would have to write instructions to go to each sheet, and then do an outer loop to go through each row, and an inner loop for each column (and the number of active cells varies by worksheet) -
Is there a way to tell (using VBA in the workbook open event) whether a user has opened a particular file by double clicking on the file icon or by using the Open Command in Excel ?
View 9 Replies View Related