Value Formats Not Playing Well Together In If Statement
Apr 29, 2012
I have a formula which lookups the sunset for a particular date.
Code:
sunset = WorksheetFunction.VLookup(tempws.Range("A9"), sun, 2, False
It returns a value in numeric format (eg. .8493055555555556).
I then compare it to a time vale in one of my userform textboxes.
Code:
If rental_end.Value > sunset Then
rental_end.value ="5:00 pm"
I have a suspicion that the tow different formats cannot provide accurate results in the If statement, and causing the result of the If statement to always be true, even when it should be false.
If this is indeed the case, how would one go about adjusting this code to provide proper comparison in the If statement?
View 3 Replies
ADVERTISEMENT
Apr 14, 2014
I have the following sub for me to play sound files:
[Code] .....
Essentially what i want to do is play one sound file, then immediately play another. I am using two subs so that can be achieved as when I was using one sub the first sound didn't play as it was immediately replaced by the second sound.
The lines of code where I call my sounds are:
[Code] .....
I thought the addition of:
[Code] .....
Would allow the first sound to play, before then playing the second sound, however this doesn't seem to be the case and the second sound doesn't play at all.
View 8 Replies
View Related
Jan 28, 2010
i have a sheet where there are 2 columns of tick boxes for good/bad input. ive been able to borrow some code that saves the sheet at a date name and based on the fact that if there is one bad tick in the bad column i would like a wav file to play showing this. likewise if there are no bad ticks then a good wav file is played instead. here is my lame code that calls to modules with the actual wav play instrucions in them, named playsound1 and playsound2 :-
Private Sub worksheet_calculate()
If [A22] > 10 Then
MsgBox "Vehicle Log Saved"
Call SaveAsTodaysDate
'End If
'if =sum[d6:d16] > 1 call soundplay1 else call soundplay2
'If Range("d6:d16").Value > 1 Then
'MsgBox "your gonna crash!"
'Call SoundPlay1 Else Call SoundPlay2
End If
End Sub
View 9 Replies
View Related
Oct 20, 2004
is there anyway of playing a sound once a user opens my excel file???
View 3 Replies
View Related
Jun 2, 2006
I am trying to figure out if it is possible to retrive the list of filter criteria in each filter of the filters collection using VBA. I could trawl through the data and retrive a unique list but I think it would take longer than if I could use what is already there.
I intend to use the list in a combo box elsewhere in the workbook......it's all to stop inquisitive little users accidently changing data and whining that thier figures are misteriously wrong.....
View 5 Replies
View Related
Dec 20, 2008
I have an automated sort descend happening all day. The top several cells are coloured green and if a cell jumps to the top from below the green cells, I would love a warning sound. Is is possible to activate a sound if a cell changes from a colour to a non colour. Preferably if any of the cells within a range of say F2: F6 changes from green to a non colour.
If it is not possible to have a change of colour to activate the macro, I could get around it by - if any cells in a range of H2:H5 >than 1 it plays a sound.
View 9 Replies
View Related
Oct 17, 2012
I am running Excel 2003, and have already searched the forum for "MP3" without avail.
Is there a way to play a MP3 file from a Sub Procedure?
J-Walk provides a way to do it with .wav and .mid files here Excel Developer Tip: Playing Sound From Excel using functions from the "winmm.dll" library.
However, .wav files are very large and I have a sizable collection of sounds which I'd like to use. If possible I'd much prefer to use .mp3 than .wav . Is there a way to do this? Preferably without Window's Media Player popping up.
View 3 Replies
View Related
Jan 5, 2003
I'm using this simple code to play a wav file I loaded into a worksheet:
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb = xlclose
I attached the macro to a button and it works. Now I need to stop the wave sound playing, by attaching another macro to a button. Is that possible? What syntax shall I use to stop playing the sound?
View 5 Replies
View Related
Sep 14, 2013
I'm using this simple code to play a wav file I loaded into a worksheet:
ActiveSheet.Shapes("Object 1").Select
Selection.Verb Verb = xlclose
I attached the macro to a button and it works. Now I need to stop the wave sound playing, by attaching another macro to a button. Is that possible? What syntax shall I use to stop playing the sound?
View 3 Replies
View Related
May 12, 2014
Can excel be made to play an alarm if a condition becomes true?
View 5 Replies
View Related
Aug 6, 2014
I have a spreadsheet with a database query attached (refreshing every 60 seconds) showing a stock summary by product.
I need the spreadsheet to play a .WAV file when values change. i.e.;
If cell A1 <6, play sound1.wav
If cell A2 <6, play sound1.wav
If cell A3 <6, play sound2.wav
If cell A4 <6, play sound2.wav
This may need to be repeated several times based on certain criteria. I have a total of about 30 cells which I need to be looked at individually (not a sum of) and to play the relevant sound.
I have seen different people try to tackle this in different ways. The ideal way would be creating alarms in VBA and triggering them using a formula, but I cannot get these to work in Excel 2010 or 2013.
View 6 Replies
View Related
May 7, 2013
I have a date base that is used as a job status board developed in excel that has locations, times, clock and other areas. What I am looking for is a way of playing a sound when the time approaches 5 min before the time in the excel cell that has been entered into the job status board...
View 3 Replies
View Related
Jul 28, 2009
I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
View 5 Replies
View Related
Oct 1, 2008
I'm trying to set up an if statement that will recognize that if a cell is FHR it will do something...but if it's PHR it will do something else. I think I found the place where I keep getting an error but I'm not sure how to go about fixing the issue.
View 2 Replies
View Related
Feb 14, 2012
I am attempting to use a previously Set variable as part of the next Set statement, pretty unsuccessfully at present.
My purpose is trying to look up
Code:
tb_SelJobID.Value
from a userform in Col Z then look across the row to Cols D,I,N,S & W (different types of work) to see if
Code:
TbSelYr.Value
matches the year selected then insert a formula in the row to the left. Then loop down to the FinalRow.
Currently my Set Found1 statement does not recognise my Found10 value. I know it will be my syntax as it always is. I have cut down the following code to display where the problem areas are, Found1 thru 5.
Code:
Sub CmdGo3_Click()
Dim Row As Range
Dim FinalRow As Long
Dim Found1, Found2, Found3, Found4, Found5, Found10 As Range
Application.ScreenUpdating = False
[code]....
Windows 7 with Excel 2010
View 9 Replies
View Related
May 5, 2014
I have an Excel Sheet which I use as Database. The database has 11 columns and I insert data with the following function:
Code:
Sub testInsert()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim i As Integer
Dim strTest As String
strTest = "test"
[Code] .......
Now I want to retrieve this data. i.e. I want all F1 where F2 and F3 are 0 AND I want them ordered descending. I'm trying to achieve this with:
Code:
Sub testSelect()
Dim adoCommand As New ADODB.Command
Dim sQuery As String
Dim mrs As New ADODB.Recordset
Dim strTest As String
strTest = "test"
[Code] ....
The result I am getting looks like this:
9
8
7
6
5
4
3
2
15
14
13
12
11
10
1
I assume, that the data is interpreted as String instead of an integer. But I explicitely stated the data as Integer when storing the data into the DB.
View 5 Replies
View Related
Aug 13, 2005
This error occcurs when i add some worksheets to a workbook from another
one. I am not completely sure (cos this is not my work actually) but it
seems to me that there is not really too much (about 4000?) "different" cell
formats in the workbook, but there is a quite lot amount of drawing objects
(grouped technical drwaings plus autocad objects which i also converted them
to bitmaps to overcome the error).
I also dont understand the restriction:
If i have 3999 cells formatted "bold" and another 2 formatted "underlined"
this should not count 4001. True?
My workbook has about 15 worksheets with each fits to 2 printing pages.
View 13 Replies
View Related
Feb 12, 2009
I have a spreadsheet with a number of columns. In column D there are the following options: 2,3,4,C,T. I want to color the cell if there is data in the cell and column D contains a value.
So for cell L17, my conditional format I have =AND(D17=2,L17<>"") - - (RED), =AND(D17=3,L17<>"") - - (YELLOW), =AND(D17=4,L17<>"") - - (GREEN).
I need to color for values of C and T: but there are only 3 conditional formats permitted. Is there any way around this?
View 14 Replies
View Related
Oct 21, 2009
I'm running excel 2000 and don't have access to mscal.ocx, so i have used the date time picker with a calendar, but the formatting of the calendar is in US format m/d/yy and I need it in UK format d/m/yy, I have narrowed it down the following piece of code, the D values are the days,
View 12 Replies
View Related
Jul 20, 2008
I have a sheet that shows sun, mon, tue........,sat - all 7 days.
Under each day there are 3 cols so 21 in total.
Data is being entered into cells each day but as the week goes on it gets more difficult to match up the col & row, to many numbers.
What I would like is a way to highlight the whole range of cells per day in a different colour but only when data has been entered under a day, so if no data then no colour fill.
I only need 2 colours, 1 for sun, tue, thurs, sat &
1 for the remaining days.
=COUNTA($A1:$C20)>0
This works for one range as a conditional format but there aren't enough options to do the 7 days.
View 9 Replies
View Related
Jan 6, 2009
I have created a very long switch statement, which is too long to be placed in one row in VBA. I have attempted to put a space and underscore at the end of one line and continue the statement on the row below by placing a comma at the start of the second line. VBA will accept my efforts, but when I run the statement in the immediate window, the following error appears.
"Invalid procedure call or argument"
I understand that there are certain rules where I can split a switch statement onto two lines, yet I do not know what they may be.
View 9 Replies
View Related
Jun 22, 2014
I need for my office a table which has one column with dates. The "issue" is, that sometimes I know the whole date (day, month, year), sometimes only the month and year and occasionally just the year. Is there a way through custom number formatting that excel behaves correctly? How would I need to set conditions to achieve this? Because now something happens which is 99% incorrect .
View 6 Replies
View Related
Mar 12, 2007
how to combine 2 conditional formats?
I'm trying to shade every other row grey so its readable with this:
=MOD(ROW(),2)
But, at the same time, would like to highlight upcoming expiry dates with this:
=AND(A2-TODAY()>=0,A2-TODAY()<=30)
View 14 Replies
View Related
Mar 13, 2007
I have data sent to me with different date formats on the same spreadsheet; I used Format - Cell and format date to this format: yyyy.mm.dd. However, only some of the data changed to this format and rest remain the same. I've tried many other ways, but didn't work.
View 14 Replies
View Related
Mar 20, 2009
I need to create a custom number format for a recorded delivery number in an excel sheet. I want it to look like AA 1234 1234 1GB in the cell even if the user enters it without spaces e.g. AA123412341GB. I have had a bit of a play around with underscores to get spaces but nothing seems to make it change.
View 4 Replies
View Related
Aug 19, 2009
I am trying to create an Excel spread sheet that keeps track of employees safety tickets and the dates they expire. Is there way to have a cell highlight to a color warning me 30 days prior to the due date listed?
Eg. John has first aid and expires on 11/15/2009. I'd like the cell that has this date to highlight red on 10/15/2009.
I have messed around with the conditional formatting and know how to format the cells color and font et., but I can not figure out the formula to use to get my results.
I am using Microsoft Office Excel 2003 (MS Office Standard)(SP3)
View 12 Replies
View Related
Jul 5, 2013
I'm trying to put cemetery records in a simple, sortable table, names and dates. The problem is that for some entries I have a full date of birth, death, or burial (e.g. 5/12/1892) and for others only a year (e.g. 1892). Is it possible to sort these? All I can get is the just years in order then the full dates at the end. My data look sort of like this:
Smith John 1892 1/1/1940 2/2/1892
Johnson Sarah 3/12/1900 1880 3/10/1900
One thing I don't know is what number format to use (text, long date, short date....) Sometimes the date I type changes after I type it (like 1892 became March 6, 1905) and sometimes it doesn't even though the cell is the same format.
View 3 Replies
View Related
Oct 10, 2008
I would like to condition one cell to display various colors, based on the information in another cell. I have attached the file for an example. The cell to be conditioned is A1, and the cell that will specify its color is B3. For example, if the value of B3 is "Red Oak", A1 would be red, and if the value is "Maple", A1 would be green.
I have more than 3 of these that need to be formatted (conditional formatting limits to 3 formats), so I'm wondering if I can do an event macro to set the formatting.
View 4 Replies
View Related
Feb 20, 2009
I have slight problem with an OnChange Event by Target.
I am looking to be able to change a cell value from one to another, create a comment block that says "person B for person A" , change the color and be done.
If the cell is empty then the code should exit the sub (not working). If the cell has already been change once then I want the comment box to show a second line of who changed what.
What I tried in the comment box, thinking it would keep the value:
View 12 Replies
View Related
Apr 19, 2009
the following i have 1500 rows and around 15 columns to change. format says 1.49-2
I need it to say 01:49.2
when I highlight it and go into format cells. and change it to min:ss.0 it changes to the following 48:28.8. i have included a file for you to see
View 5 Replies
View Related
