Hi New to VBA so apologies in advance if a dumb question! I have managed to get to the following which is working well but I now want corresponding cells in the "k" column to change to specific colours based on the data in "I" and "J" columns and seem to have put myself in a corner - what os the best way to write such a request please? 'Convert to FMECA format, and set matrix row If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then Range("i" & iRow).Value = "A - Almost Certain" iconsequence = 1 ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or LCase(consequence) = "l" Or LCase(consequence) = "likely" Then Range("i" & iRow).Value = "B - Likely" iconsequence = 2 ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or LCase(consequence) = "p" Or LCase(consequence) = "possible" Then Range("i" & iRow).Value = "C - Possible" iconsequence = 3 ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then Range("i" & iRow).Value = "D - Unlikely" iconsequence = 4 ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or LCase(consequence) = "r" Or LCase(consequence) = "rare" Then Range("i" & iRow).Value = "E - Rare" iconsequence = 5 'Convert to FMECA format, and set matrix column End If If likelihood = "5 - Catastrophic" Or likelihood = "5" Or LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = "catastrophic" Then Range("j" & iRow).Value = "5 - Catastrophic" ilikelihood = 5 ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then Range("j" & iRow).Value = "4 - Major" ilikelihood = 4 ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = "moderate" Then Range("j" & iRow).Value = "3 - Moderate" ilikelihood = 3 ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then Range("j" & iRow).Value = "2 - Moderate" ilikelihood = 2 ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = "insignificant" Then Range("j" & iRow).Value = "1 - Insignificant" ilikelihood = 1 Else ilikelihood = "0" -- Thanks as always Lise

0 |

1/28/2010 3:55:01 AM

Lise I have rewritten the code and used SELECT/CASE statements which will reduce the code..The line Range("K" & irow).Interior.ColorIndex = 3 + iconsequence will change the color of cell K based on the value of the variable iconsequence. Hope this helps '/Convert to FMECA format, and set matrix row Select Case UCase(Left(consequence, 1)) Case "A" Range("i" & irow).Value = "A - Almost Certain" iconsequence = 1 Case "B", "L" Range("i" & irow).Value = "B - Likely" iconsequence = 2 Case "C", "P" Range("i" & irow).Value = "C - Possible" iconsequence = 3 Case "D", "U" Range("i" & irow).Value = "D - Unlikely" iconsequence = 4 Case "E", "R" Range("i" & irow).Value = "E - Rare" iconsequence = 5 '/Convert to FMECA format, and set matrix column End Select Range("K" & irow).Interior.ColorIndex = 3 + iconsequence Select Case UCase(Trim(Left(likelihood, 2))) Case "5", "CA" Range("j" & irow).Value = "5 - Catastrophic" ilikelihood = 5 Case "4", "MA" Range("j" & irow).Value = "4 - Major" ilikelihood = 4 Case "3", "MO" Range("j" & irow).Value = "3 - Moderate" ilikelihood = 3 Case "2", "MI" Range("j" & irow).Value = "2 - Moderate" ilikelihood = 2 Case "1", "IN" Range("j" & irow).Value = "1 - Insignificant" ilikelihood = 1 Case Else ilikelihood = 0 End Select -- Jacob "Lise" wrote: > Hi > > New to VBA so apologies in advance if a dumb question! > > I have managed to get to the following which is working well but I now want > corresponding cells in the "k" column to change to specific colours based on > the data in "I" and "J" columns and seem to have put myself in a corner - > what os the best way to write such a request please? > > 'Convert to FMECA format, and set matrix row > If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or > LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then > Range("i" & iRow).Value = "A - Almost Certain" > iconsequence = 1 > ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or > LCase(consequence) = "l" Or LCase(consequence) = "likely" Then > Range("i" & iRow).Value = "B - Likely" > iconsequence = 2 > ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or > LCase(consequence) = "p" Or LCase(consequence) = "possible" Then > Range("i" & iRow).Value = "C - Possible" > iconsequence = 3 > ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or > LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then > Range("i" & iRow).Value = "D - Unlikely" > iconsequence = 4 > ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or > LCase(consequence) = "r" Or LCase(consequence) = "rare" Then > Range("i" & iRow).Value = "E - Rare" > iconsequence = 5 > > 'Convert to FMECA format, and set matrix column > End If > If likelihood = "5 - Catastrophic" Or likelihood = "5" Or > LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = > "catastrophic" Then > Range("j" & iRow).Value = "5 - Catastrophic" > ilikelihood = 5 > ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) > = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then > Range("j" & iRow).Value = "4 - Major" > ilikelihood = 4 > ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or > LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = > "moderate" Then > Range("j" & iRow).Value = "3 - Moderate" > ilikelihood = 3 > ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) > = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then > Range("j" & iRow).Value = "2 - Moderate" > ilikelihood = 2 > ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or > LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = > "insignificant" Then > Range("j" & iRow).Value = "1 - Insignificant" > ilikelihood = 1 > Else > ilikelihood = "0" > -- > Thanks as always > > Lise

0 |

1/28/2010 4:49:01 AM

Hi Jacob sorry its taken me a while to come back I have been trying to fiddle - but to no avail - perhaps i should have given you the whole page. I just cant get the colours to do what I want - tried entering your part but still nothing. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim old As Integer Dim matrix(1 To 6, 1 To 4) As String Dim cellcolour(1 To 6, 1 To 4) As Integer iMatrixRow = 1 iMatrixColumn = 1 For iColumn = 3 To 6 For iRow = 5 To 10 matrix(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, iColumn).Value cellcolour(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, iColumn).Interior.ColorIndex iMatrixRow = iMatrixRow + 1 Next iRow iMatrixRow = 1 iMatrixColumn = iMatrixColumn + 1 Next iColumn 'Change E to H 'Change C to I 'Change F to J For iRow = 6 To 100 consequence = Range("I" & iRow).Value likelihood = Range("J" & iRow).Value 'determine old cell contents to see if it changes. If not, do not update colour else it will affect copy and paste function on the worksheet. oldrisk = Range("K" & iRow).Value 'Convert to FMECA format, and set matrix row If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then Range("i" & iRow).Value = "A - Almost Certain" iconsequence = 1 ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or LCase(consequence) = "l" Or LCase(consequence) = "likely" Then Range("i" & iRow).Value = "B - Likely" iconsequence = 2 ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or LCase(consequence) = "p" Or LCase(consequence) = "possible" Then Range("i" & iRow).Value = "C - Possible" iconsequence = 3 ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then Range("i" & iRow).Value = "D - Unlikely" iconsequence = 4 ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or LCase(consequence) = "r" Or LCase(consequence) = "rare" Then Range("i" & iRow).Value = "E - Rare" iconsequence = 5 'Convert to FMECA format, and set matrix column End If If likelihood = "5 - Catastrophic" Or likelihood = "5" Or LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = "catastrophic" Then Range("j" & iRow).Value = "5 - Catastrophic" ilikelihood = 5 ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then Range("j" & iRow).Value = "4 - Major" ilikelihood = 4 ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = "moderate" Then Range("j" & iRow).Value = "3 - Moderate" ilikelihood = 3 ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then Range("j" & iRow).Value = "2 - Moderate" ilikelihood = 2 ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = "insignificant" Then Range("j" & iRow).Value = "1 - Insignificant" ilikelihood = 1 Else ilikelihood = "0" End If 'set matrix number in HRI cell If ilikelihood >= 1 And ilikelihood <= 6 And iconsequence >= 1 And iconsequence <= 4 Then Range("K" & iRow).Value = matrix(ilikelihood, iconsequence) 'update new cell colours, only if HRI number has changed If Range("K" & iRow).Value <> oldrisk Then Range("K" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, iconsequence) End If 'update cell colour if matrix colour changes If Range("K" & iRow).Interior.ColorIndex <> cellcolour(ilikelihood, iconsequence) Then Range("K" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, iconsequence) End If Else 'delete matrix number in HRI cell Range("K" & iRow).Value = "" 'clear old HRI cell colour if no longer valid Range("K" & iRow).Interior.ColorIndex = 0 End If Next iRow iMatrixRow = 1 iMatrixColumn = 1 For iColumn = 3 To 6 For iRow = 5 To 10 matrix(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, iColumn).Value cellcolour(iMatrixRow, iMatrixColumn) = Sheets("Tables").Cells(iRow, iColumn).Interior.ColorIndex iMatrixRow = iMatrixRow + 1 Next iRow iMatrixRow = 1 iMatrixColumn = iMatrixColumn + 1 Next iColumn 'Change H to L 'Change I to M 'Change J to N For iRow = 6 To 100 consequence = Range("M" & iRow).Value likelihood = Range("N" & iRow).Value 'determine old cell contents to see if it changes. If not, do not update colour else it will affect copy and paste function on the worksheet. oldrisk = Range("O" & iRow).Value 'Convert to FMECA format, and set matrix row If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or LCase(consequence) = "ac" Or LCase(consequence) = "almost certain" Then Range("M" & iRow).Value = "A - Almost Certain" iconsequence = 1 ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or LCase(consequence) = "l" Or LCase(consequence) = "likely" Then Range("M" & iRow).Value = "B - Likely" iconsequence = 2 ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or LCase(consequence) = "p" Or LCase(consequence) = "possible" Then Range("M" & iRow).Value = "C - Possible" iconsequence = 3 ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then Range("M" & iRow).Value = "D - Unlikely" iconsequence = 4 ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or LCase(consequence) = "r" Or LCase(consequence) = "rare" Then Range("M" & iRow).Value = "E - Rare" iconsequence = 5 Else iconsequence = "0" End If 'Convert to FMECA format, and set matrix column If likelihood = "5 - Catastrophic" Or likelihood = "5" Or LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = "catastrophic" Then Range("N" & iRow).Value = "5 - Catastrophic" ilikelihood = 5 ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then Range("N" & iRow).Value = "4 - Major" ilikelihood = 4 ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = "moderate" Then Range("N" & iRow).Value = "3 - Moderate" ilikelihood = 3 ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then Range("N" & iRow).Value = "2 - Moderate" ilikelihood = 2 ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = "insignificant" Then Range("N" & iRow).Value = "1 - Insignificant" ilikelihood = 1 Else ilikelihood = "0" End If 'set matrix number in HRI cell If ilikelihood >= 1 And ilikelihood <= 6 And iconsequence >= 1 And iconsequence <= 4 Then Range("O" & iRow).Value = matrix(ilikelihood, iconsequence) 'update new cell colours, only if HRI number has changed If Range("O" & iRow).Value <> oldrisk Then Range("O" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, iconsequence) End If 'update cell colour if matrix colour changes If Range("O" & iRow).Interior.ColorIndex <> cellcolour(ilikelihood, iconsequence) Then Range("O" & iRow).Interior.ColorIndex = cellcolour(ilikelihood, iconsequence) End If Else 'delete matrix number in HRI cell Range("O" & iRow).Value = "" 'clear old HRI cell colour if no longer valid Range("O" & iRow).Interior.ColorIndex = 0 End If Next iRow End Sub -- Thanks as always Kiwi "Jacob Skaria" wrote: > Lise > > I have rewritten the code and used SELECT/CASE statements which will reduce > the code..The line Range("K" & irow).Interior.ColorIndex = 3 + iconsequence > will change the color of cell K based on the value of the variable > iconsequence. Hope this helps > > '/Convert to FMECA format, and set matrix row > Select Case UCase(Left(consequence, 1)) > Case "A" > Range("i" & irow).Value = "A - Almost Certain" > iconsequence = 1 > Case "B", "L" > Range("i" & irow).Value = "B - Likely" > iconsequence = 2 > Case "C", "P" > Range("i" & irow).Value = "C - Possible" > iconsequence = 3 > Case "D", "U" > Range("i" & irow).Value = "D - Unlikely" > iconsequence = 4 > Case "E", "R" > Range("i" & irow).Value = "E - Rare" > iconsequence = 5 > '/Convert to FMECA format, and set matrix column > End Select > Range("K" & irow).Interior.ColorIndex = 3 + iconsequence > > Select Case UCase(Trim(Left(likelihood, 2))) > Case "5", "CA" > Range("j" & irow).Value = "5 - Catastrophic" > ilikelihood = 5 > Case "4", "MA" > Range("j" & irow).Value = "4 - Major" > ilikelihood = 4 > Case "3", "MO" > Range("j" & irow).Value = "3 - Moderate" > ilikelihood = 3 > Case "2", "MI" > Range("j" & irow).Value = "2 - Moderate" > ilikelihood = 2 > Case "1", "IN" > Range("j" & irow).Value = "1 - Insignificant" > ilikelihood = 1 > Case Else > ilikelihood = 0 > End Select > > -- > Jacob > > > "Lise" wrote: > > > Hi > > > > New to VBA so apologies in advance if a dumb question! > > > > I have managed to get to the following which is working well but I now want > > corresponding cells in the "k" column to change to specific colours based on > > the data in "I" and "J" columns and seem to have put myself in a corner - > > what os the best way to write such a request please? > > > > 'Convert to FMECA format, and set matrix row > > If consequence = "A - Almost Certain" Or LCase(consequence) = "a" Or > > LCase(consequence) = "a" Or LCase(consequence) = "almost certain" Then > > Range("i" & iRow).Value = "A - Almost Certain" > > iconsequence = 1 > > ElseIf consequence = "B - Likely" Or LCase(consequence) = "b" Or > > LCase(consequence) = "l" Or LCase(consequence) = "likely" Then > > Range("i" & iRow).Value = "B - Likely" > > iconsequence = 2 > > ElseIf consequence = "C - Possible" Or LCase(consequence) = "c" Or > > LCase(consequence) = "p" Or LCase(consequence) = "possible" Then > > Range("i" & iRow).Value = "C - Possible" > > iconsequence = 3 > > ElseIf consequence = "D - Unlikely" Or LCase(consequence) = "d" Or > > LCase(consequence) = "u" Or LCase(consequence) = "unlikely" Then > > Range("i" & iRow).Value = "D - Unlikely" > > iconsequence = 4 > > ElseIf consequence = "E - Rare" Or LCase(consequence) = "e" Or > > LCase(consequence) = "r" Or LCase(consequence) = "rare" Then > > Range("i" & iRow).Value = "E - Rare" > > iconsequence = 5 > > > > 'Convert to FMECA format, and set matrix column > > End If > > If likelihood = "5 - Catastrophic" Or likelihood = "5" Or > > LCase(likelihood) = "ca" Or LCase(likelihood) = "5" Or LCase(likelihood) = > > "catastrophic" Then > > Range("j" & iRow).Value = "5 - Catastrophic" > > ilikelihood = 5 > > ElseIf likelihood = "4 - Major" Or likelihood = "4" Or LCase(likelihood) > > = "ma" Or LCase(likelihood) = "4" Or LCase(likelihood) = "major" Then > > Range("j" & iRow).Value = "4 - Major" > > ilikelihood = 4 > > ElseIf likelihood = "3 - Moderate" Or likelihood = "3" Or > > LCase(likelihood) = "mo" Or LCase(likelihood) = "3" Or LCase(likelihood) = > > "moderate" Then > > Range("j" & iRow).Value = "3 - Moderate" > > ilikelihood = 3 > > ElseIf likelihood = "2 - Minor" Or likelihood = "2" Or LCase(likelihood) > > = "mi" Or LCase(likelihood) = "2" Or LCase(likelihood) = "minor" Then > > Range("j" & iRow).Value = "2 - Moderate" > > ilikelihood = 2 > > ElseIf likelihood = "1 - Insignificant" Or likelihood = "1" Or > > LCase(likelihood) = "in" Or LCase(likelihood) = "1" Or LCase(likelihood) = > > "insignificant" Then > > Range("j" & iRow).Value = "1 - Insignificant" > > ilikelihood = 1 > > Else > > ilikelihood = "0" > > -- > > Thanks as always > > > > Lise

0 |

2/4/2010 2:07:01 AM

I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

...

I upgraded to Outlook 2002 while leaving Word 2000. Is there a way to make word the e-mail editor and to ensure all messages come in as rich text? Word and Outlook versions must match to do that. "Dave" <dmichelman@chesapeakeacademy.com> wrote in message news:016701c34d66$b2bed680$a601280a@phx.gbl... > I upgraded to Outlook 2002 while leaving Word 2000. Is > there a way to make word the e-mail editor and to ensure > all messages come in as rich text? ...

I'm looking to send an email that has a song playing in the background when the recipient opens it. I'm using Outlook 2002 for XP and when I select Format, and Background there is not option for Sound. You can do this when you set Word as the e-mail editor; http://www.howto-outlook.com/howto/addingsound.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Anthony" <Anthony@discussions.microsoft.com> wrote in message news:593CCEA2-D565-42F...

Hello, I had many macros that i want to run them all at once but not upon the opening of the excel workbook. i want to have one command button that runs them all. is it feasible?? Samer Gamal Hi Create another Macro that calls each other macro in turn. Sub RunAll() Call Macro1 Call Macro2 .. .. .. Call MacroX End Sub -- Regards Roger Govier <samergadelrab@gmail.com> wrote in message news:d57632d6-d468-4a71-b8f6-c9a87ce9150e@p25g2000hsf.googlegroups.com... > Hello, > I had many macros that i want to run them all at once but not upon the > opening of the excel workbook. i ...

One of my sheets has several identical forms with different data in the cells. There is a button on each form linking that sheet to another. When I press that button it goes to the other sheet and autofill 3 cells from the previous sheet and form. What I need to do, as the new sheet has space for multiple entries, is when I click the button from another form it recognises that data already exists in the 3 cells and automatically fills the next 3 available unprotected cells. I have been at this for weeks and I am unable to find a solution in the help file. Much obliged to any assist...

I am trying to make a formula that gives me a count of how many row have the same date and time...i have tried everything i can think o and can't seem to get it...any help is appreciated! i.e. saturday 14:00 saturday 11:00 saturday 14:00 saturday 14:00 saturday 14:00 monday 14:00 monday 14:00 tuesday 4:00 tuesday 14:00 tuesday 1:00 there will be 7 fields...one for each day and time...so in the abov example they should display:(i left a lot of fields out below...onl mentioned the ones that are used in the example) saturday/14:00 - 4 saturday/11:00...

Hi all. I have an Access database with linked tables to SQL Server 2005. I have a table with a field defined as a VarBinary which is to be used to store documents (word, excel etc) Does anyone have sample VBA code to (1) insert or update the document to the linked table (2) retrieve the data and write to the file system. I have done this via .NET successfully but need to do it via VBA and am struggling to do this as my VBA experience is rusty to say the least. All help and direction greatly appreciated. Thanks Raj On 16 Jul, 17:00, Londoner101 <rajpate...@hotmail.com> wrote: >...

Hi How do I add documentation/desriptions for VBA functions or function variables? I have tried to add a description to a function's properties under its Memeber Options in VBA but the description is not displayed when the function is called in MS Excel. Jacques Jacques, You need Laurent Longre's FUNCUSTOMIZE utility .Get it at http://longre.free.fr/english/ -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Jacques Maartens" <someone@somewhere.com> wrote in message news:c0...

Does anybody know why, no matter what colour is selected for the text on the Touchscreen keyboard, it always shows as white!!!? ...

Hi, I have columns that I tried to change the cell information t another cell that have to be the blank for a row that contains directo name in cell A and for the movie titles I have to have director name instead of the titles.I totally lost, please help me, the data in cel B sholuld have to be, A 1 D:B.Jane 2 T:Back to the future 3 T:Aliens 4 T:X-files 5 D:Adam Mathew 6 T:Core 7 T:Titanic B 1 (empty) 2 D:B.Jane 3 D:B.Jane 4 D:B.Jane 5 (empty) 6 D:Adam Mathew 7 D:Adam Mathew Thanks in advance for any suggestions -- Message posted from http://www.ExcelForum.com please try the formula list...

Using MS Access 2007 VBA once I have opened a query in PivotChart view with the following: DoCmd.OpenQuery "MyQuery", acViewPivotChart How can I set the following: 1) Filter 2) Data 3) Series 4) Category The user can "Drag & Drop" items from the popup Field List. How can I complete this using VBA? Here is a solution to my situation that I have been able to piece together and get to a rough state using a single form rather than multiples and I do NOT have to use the OWC reference to make it work. It is incomplete and still under construction ...

I am trying to adjust the width of an individual cell and all I am able to do is adjust the width of the entire column. Any suggestions? Thank you That is all you can do. Ola "Tabitha" <Tabitha@discussions.microsoft.com> skrev i meddelandet news:E138E608-96BB-4888-B886-36598D36E3CF@microsoft.com... >I am trying to adjust the width of an individual cell and all I am able to >do > is adjust the width of the entire column. Any suggestions? > > Thank you You can select two or more cells and merge them (Format>Cells, Alignment tab) However, this can cau...

Hello all, I am trying to use ADODC control in my application. I want to show the fields of a record in edit boxes and then enable scrolling via the ADODC control to move to another record. I am unable to find any example of adodc usage and implementation in vc++ on the internet. If someone has any sample code, any help is greatly appreciated. Thanks ...

Hi, What VBA code should I use in a worksheet so it will print a header & Footer? On Nov 28, 12:43=A0pm, nickbest_l...@hotmail.com wrote: > Hi, > What VBA code should I use in a worksheet so it will print a header & > Footer? Have you tried doing it manually while recording a macro...?? On Nov 28, 9:29=A0pm, Don Guillett <dguille...@gmail.com> wrote: > On Nov 28, 12:43=A0pm, nickbest_l...@hotmail.com wrote: > > > Hi, > > What VBA code should I use in a worksheet so it will print a header & > > Footer? > > Have you tried doing it manua...

Hi. I having a problem with setting up the option "Use Different Delivery times for oversize messages" When I set the option in the SMTP connector its all look fine. And when I send a mail that is big in size than I can see in the queues that it is schedule to be sent at night. the problem : when it's time to send the mail the server is not sending them... or sometimes it is sending only one mail and the rest is "stuck" Any ides??? Thanks in advance Another thing that I have been notice is that in the queue's you can see that only one message is schedule...

I have a worksheet named Items. I am trying to refer to the values in another worksheet called Dist (same workbook). The value in the Dist worksheet at F6 = 1906, and the value in Dist worksheet at AO6 = 100. So at Items A1, in the formula box, I type =Concatenate(Dist!F6," ",Dist!AO6) because I want to refer to the numbers as text. But instead of showing me 1906 100 all I see is the formula I typed instead of the formula's value. When I type the same formula in a cell for the Dist worksheet it works fine, but when I change to another worksheet it doesn't work.What am I doi...

I have a simple worksheet to amortise a loan. I would like to use a formula to extend the rows downwards to the number of payments (Number of years x Number of payments per year) using a formula. For example, if there were 26 payments per year over 1 year, there should be 26 rows. Now if I change the number of years to 2 years, two things should happen: 1. The number of rows should expand to 52 from 26 2. The sum of the interest paid should include these extra rows.i.e =Sum(rownumber 1 to row number 52) instead of Sum (rownumber 1 to rownumber 26). Currently I am using a simple formula alon...

Is anyone using the Auto Accept Agent and has customized the agent's XML config file to give customized repsones for individual mailboxes or change from the agent default settings? I have followed the Auto Accept Agent administration guide to the letter on this with no luck. the agent always uses its default settings. The XML file is valid and there are no messages in the logs. Restarting the service doesn't help either. This is on an Exchange 2003 Enterprise edition server running on W2K3 Server SP1. I haven't run across anyone who has it working, only a few like me who c...

Hi, We have found that whenever an email is sent to the distribution list 'all at HQ', an NDR (below) is returned for one of the users. When an email is sent to her directly it is fine however. I looked this error up, and the suggestion was that the user's email address has changed, but this is not the case. This happened previously, and was resolved very easily by just removing the user object from the distribution group and re adding it. I did this again for all the groups, but the error still occurs when a mass mail is sent out. The full error is below: Your message did not ...

Thanks for the help....... But is there a way to create a macro that will save the orriginal fil periodically, instead of relying on the autosave function on Excel? Maybe using the SaveAs and Wait functions? Celtic_Avenger :confused: :confused: :confused: :confused: :confused -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforum.com/showthread.php?threadid=26126 Look at application.ontime Chip Pearson explains i...

Excel 2000 I use Edit/Find frequently, but have a hard time seeing the 'found' cell. Is there a way to change the look (background color) used for the active cell? I have come across the "RowLiner" add-in and other fancy solution, but I would just like something simple, like changing the active cell color. Thanks here is a link with directions to highlight the active cell Highlight the Active Cell, by Andrew Siffert http://blog.livedoor.jp/andrewe/archives/50061563.html Warm Regards, Crystal remote programming and training Access Basics 8-part free tutorial that cove...

I'm trying to sort upon a given column and I get the error message stating that "Merged cells must be of the same size." I know what merged cells are, but the problem is, I cannot find ANY cells in the column in which I am sorting that are merged. So does this rule apply to ANY CELL in ANY column? If so, how do I bypass or shut off this feature so that I can perform what I thought was going to be a simple sorting of date on a single field? Select all the cells in the entire worksheet and in Format > Cells... clear the merge checkbox -- Gary's Student gsnu200706 ...

I had never used the archiving ability of Outlook until a few days ago. For years, I had been saving my messages in the "Saved Mail" folder, using a complex tree structure. Eventually, however, this tree became so unwieldy that I asked someone here how to save these emails to Word. They were kind enough to help me, telling me that Outlook would not export to Word, and suggested that I archive them instead. I worked through the archival feature, and was very pleased with the way it saved all the emails I had carefully preserved for the last several years. I even set up a re...

Thanks! On Tue, 26 Oct 2004 09:08:55 +0200, "Clau" <clau@edil.ro> wrote: >Thanks! > Howdy, Click View | Go To | News... Outlook will then launch Outlook Express to read newsgroups. Outlook itself has no news functionality... HTH, -- Kenneth If you email... Please remove the "SPAMLESS." ...