$$Excel-Macros$$ more than 3 rules in condtional formatting
Hi, M using excel 2003, and say i need to have this below rule for a cell if value is india, background color blue if value is aus, background color yellow if value is sa, background color pink if value is eng, background color red if value is sl, background color navyblue m using conditional formatting, but it gives only 3 rules, is there any workaround for this Thanks Kishore --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Run Macro Automatically
Hello, I need help in 2 macros. Any suggestions will be greatly appreciated. thanks in advance. 1) Is it possible to run a macro automatically if certain cell value changes? e.g Cell A1 has a value which comes from a formula and 1) Can I schedule a fixed time to run a macro. --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: VBA code to search for worksheet with matching date
Hi, Try this code dim dtDate as Date dtDate = Thisworkbook.Names(IDO_Date).RefersToRange.Value for i = 2 to Sheets.count sheets(i).select if date(Range(B2).value)=dtdate then Exit For end if Next On May 19, 7:44 pm, Doug dsrmccl...@gmail.com wrote: Hello, I have a workbook with a separate page for each date. The date is always in cell B2. On the first page of the workbook I want to enter the date into a cell named IDO_Date and click on a button to go to the worksheet for that date. Can someone please show me the easiest code to assign to the button to do this? Thanks. --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: VBA code to search for worksheet with matching date
hello i downloaded the (abletoextract professional) software for converting pdf to word , for which excel group provided me a 16 digit pin also, but now after 30 days i want to use this software , its not working, if any can again provide me that pin no. Thans in advance Puneet jain Power Finance Corporation Delhi 9899068873 --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: SQL 2005
You have to enclose your parameter in ': see below; SELECT user_code FROM ousr WHERE user_code= ' busca ' 2009/5/19 Erikito1981 erikito1...@gmail.com Hello everyone I have a problem, I have this code which works fine me if I use is the field number but if the data is character sends me an error message This is the code busca = TextBox1.Text Set Archivo = New ADODB.Recordset Sqlc = Driver={SQL Server};Server=SAPSERVER;Database=INTEGRADORA;Uid=sa;Pwd=B1Admin; Cnn.Open Sqlc Sql = SELECT user_code FROM ousr WHERE user_code= busca Error en Timepo de ejecucion -2147217900(80040-e14) invalid Column name '1234' Value 1234 is the value of the variable busca Thanks. -- Fabio L Lemos email: flnle...@gmail.com --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Disable/hide minimize restore buttons (Excel 2007)
Hi! I have to disable or hide minimize and restore buttons in excel system menu. But this code works correctly in excel 2003 but not in excel 2007. Excel 2007 hide only minimize button! --- Type MENUITEMINFO cbSize As Long fMask As Long fType As Long fState As Long wID As Long hSubMenu As Long hbmpChecked As Long hbmpUnchecked As Long dwItemData As Long dwTypeData As String cch As Long End Type 'Menu item constants. Const SC_SIZE As Long = HF000 Const SC_MOVE As Long = HF010 Const SC_MINIMIZE As Long = HF020 Const SC_MAXIMIZE As Long = HF030 Const SC_NEXTWINDOW As Long = HF040 Const SC_PREVWINDOW As Long = HF050 Const SC_CLOSE As Long = HF060 Const SC_VSCROLL As Long = HF070 Const SC_HSCROLL As Long = HF080 Const SC_MOUSEMENU As Long = HF090 Const SC_KEYMENU As Long = HF100 Const SC_ARRANGE As Long = HF110 Const SC_RESTORE As Long = HF120 Const SC_TASKLIST As Long = HF130 Const SC_SCREENSAVE As Long = HF140 Const SC_HOTKEY As Long = HF150 'SetMenuItemInfo fState constants. Const MFS_GRAYED As Long = H3 Const MFS_DEFAULT As Long = H1000 'SetMenuItemInfo fMask constants. Const MIIM_STATE As Long = H1 Const MIIM_ID As Long = H2 'SendMessage constants. Const WM_NCACTIVATE As Long = H86 'Window constants Const WS_CAPTION = HC0 Const WS_CHILD = H4000 Const WS_HSCROLL = H10 Const WS_VSCROLL = H20 Const WS_VISIBLE = H1000 Const WS_CLIPCHILDREN = H200 Const WS_CLIPSIBLINGS = H400 Const WS_BORDER = H80 Const WS_TABSTOP = H1 Const WS_POPUP = H8000 Const WS_SYSMENU = H8 Const WS_THICKFRAME = H4 Const WS_MINIMIZEBOX = H2 Const WS_MAXIMIZEBOX = H1 Const WS_DLGFRAME = H40 Private Const WS_EX_TOPMOST = H8 Private Const WS_EX_CLIENTEDGE = H200 Private Const WS_EX_TRANSPARENT = H20 Const GWL_STYLE As Long = (-16) Const RDW_INVALIDATE = H1 ' Window Styles Declare Function SetMenuItemInfo Lib user32 Alias SetMenuItemInfoA (ByVal hMenu As Long, ByVal un As Long, ByVal bool As Boolean, lpcMenuItemInfo As MENUITEMINFO) As Long Declare Function GetDesktopWindow Lib user32 () As Long Declare Function FindWindowEx Lib user32 Alias FindWindowExA (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long Declare Function GetCurrentProcessId Lib kernel32 () As Long Declare Function GetWindowThreadProcessId Lib user32 (ByVal hwnd As Long, ByRef lpdwProcessId As Long) As Long Declare Function GetSystemMenu Lib user32.dll (ByVal hwnd As Long, ByVal bRevert As Long) As Long Declare Function SendMessage Lib user32 Alias SendMessageA (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long Declare Function GetWindowLong Lib user32 Alias GetWindowLongA (ByVal hwnd As Long, ByVal nIndex As Long) As Long Declare Function SetWindowLong Lib user32 Alias SetWindowLongA (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long Declare Function DrawMenuBar Lib user32 (ByVal hwnd As Long) As Boolean Declare Function RedrawWindow Lib user32 (ByVal hwnd As Long, lprcUpdate As RECT, ByVal hrgnUpdate As Long, ByVal fuRedraw As Long) As Long Declare Function GetWindowRect Lib user32 (ByVal hwnd As Long, lpRect As RECT) As Long Declare Function DeleteMenu Lib user32 (ByVal hMenu As Long, ByVal nPosition As Long, ByVal wFlags As Long) As Boolean Sub DisableAppMinimize() Dim hWndExcel As Long Dim hSysMenu As Long Dim retVal As Long Dim MI_Info As MENUITEMINFO hWndExcel = GetWindowHandle(XLMAIN, Application.Caption) hSysMenu = GetSystemMenu(hWndExcel, 0) retVal = GetWindowLong(hWndExcel, GWL_STYLE) retVal = retVal And Not (WS_MINIMIZEBOX) retVal = SetWindowLong(hWndExcel, GWL_STYLE, retVal) ' Delete context menu retVal = DeleteMenu(hSysMenu, SC_MAXIMIZE, 0) retVal = DeleteMenu(hSysMenu, SC_MINIMIZE, 0) retVal = DeleteMenu(hSysMenu, SC_RESTORE, 0) retVal = DeleteMenu(hSysMenu, SC_MOVE, 0) retVal = DeleteMenu(hSysMenu, SC_SIZE, 0) 'retVal = DeleteMenu(hSysMenu, SC_CLOSE, 0) retVal = DeleteMenu(hSysMenu, 0, H400) DrawMenuBar (hWndExcel) retVal = SendMessage(hWndExcel, WM_NCACTIVATE, True, 0) End Sub --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com
$$Excel-Macros$$ Re: Leading zeros
Dear John and all Apart from the many solutions suggesting formatting as text or adding the ' sign, you could custom format the cell, thus retaining the number format and having the leading zeroes at the same time. For example if your numbers are of a fixed length of 6 digits so that 15 is to be displayed as 15, you could custom format the cell/cells using the format cell dialogue box and typing 00 using custom format. Anil Kumar Agarwala Guwahati : 781001 Assam akagarw...@gmail.com -Original Message- From: excel-macros@googlegroups.com [mailto:excel-mac...@googlegroups.com] On Behalf Of john Sent: Tuesday, May 19, 2009 7:22 PM To: MS EXCEL AND VBA MACROS Subject: $$Excel-Macros$$ Leading zeros I have a basi questions. When I try to enter a number with a leading zero, it ignores the zero and enters the rest. Where do I go to correct this so all numbers including the leading zero be entered and displayed? --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Fwd: Help required
Hi Dave and Team, Thanks for the help provided. I have done this successfully with out any issues. once again thank you all for your valuable and swift response as well. Regards, Sharma On Wed, May 13, 2009 at 10:50 PM, Fabio Lemos flnle...@gmail.com wrote: Hi, It's possible to use Validation, but it need to be custom and you'll need a auxiliar column. But Validation doens't have the possibility of warning in some cases and error in other. I created a solution that uses validation for the main rules and for the first 0 I used conditional formating. see the attached file. Regards! 2009/5/13 amrahs k amrahs...@gmail.com Hi Harmeet, I have attached one sample workbook that contains a query for data validation. I just need a macro for the same. Thanks for your help. Regards, Sharma On Wed, May 6, 2009 at 10:58 PM, Harmeet Singh harmeet.hew...@gmail.com wrote: Hi, Please see attached file. On Wed, May 6, 2009 at 5:53 PM, amrahs k amrahs...@gmail.com wrote: Hi Team, I need your help it is very urgent! Thanks, Sharma -- Forwarded message -- From: amrahs k amrahs...@gmail.com Date: Wed, May 6, 2009 at 5:33 PM Subject: Help required To: gyanjai...@gmail.com Hi Jain, I have attached the sample spreadsheet that i was working upon for my project. The requirement would be- Count the data that has been entered in each cell under coulumn D6:D17, F6:F17, H6:H17 and so on. (every alternate column) I want the macro to evaluate the total count of each alternate coulumn though I have few conditions. condition 1: count all the data entered in each cell (text or numeric value) condition 2: do not count the data NA It would be helpful if the macro populated the result in a message box- Ex: The Total Count is 13 (The attached document has 17 data in the range D6:D17, what i want to exclude the value NA and produce the result as 13). similarly for every alternate column. I believe you could do this since am not familiar with VBA macro. Thanx in advance. Regards, Sharma -- Thanks Regards, Harmeet Singh -- Fabio L Lemos email: flnle...@gmail.com --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Macro or add-in to Highlight Current Row in Color in any spreadsheet I want
Folks: G'day. I am an absolute novice at VBA and macros. I do get around by adapting solutions provided by more knowledgeable and learned people. So please be understanding. This is the issue I am trying to resolve. I need to highlight the current row I am using and I have the perfect code to use. The catch is that I would like to have it as a button (little smiley face) on the toolbar so that I can use it on any spreadsheet I am creating/using. I do not intend to distribute the code. I have found the perfect code - Thanks to David McRitchie. (http:// www.mvps.org/dmcritchie/excel/event.htm) This is the code I used: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.colorindex = 0 'Turn off previous use If Cells(1, 1) = . Then Exit Sub Target.EntireRow.Interior.colorindex = 38 End Sub However as this code is limited to the sheet in which the code is placed, I then adapted it so that it is available on all worksheets in a workbook. Thanks to Damon Ostrander.(http://www.mrexcel.com/archive/ VBA/29582.html) I pasted a WorkbookSheetSelectionChange event in the ThisWorkbook event code pane and the code now works perfectly on all worksheets in a workbook. But I have to paste it to every workbook that I need to use the code in. With my limited knowledge, I then tried to turn it into an add-in ( .xla file) following instructions I found on the net 1.Insert Module 2. Pasted unchanged code into module 3. ALT-Q to return to sheet and saved as .xla files 4. Restarted Excel and ticked the add-in under Tools. 5. Tried to customise button - Toolbars - customise - command-macro- moved Smiley face to toolbar - Assign macro.No macro available Please help!!! I am not competent enough to build a toolbar or write additional code to create a button. I would like to be able to use this code at my discretion on any spreadsheet without having to paste the code every time into the current spreadsheet. Thanks in advance and God bless, OR --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Help with Macros to sum data
Dear Friends,Can someone help me to run a macro to calculate the sum of a cell across sheets...for Example Sheet 1 B2, Sheet 2 B2 and Sheet 3 B3 have some numbers...now i want to run a macro that will take the sum of cell B2 across the sheets, in Summary sheet B2. Thanks Regards, Vandana --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ want a macro code
Hi all!i have attached a file, there is 5 fields in the file my requirement is, as i put city name in city field, then all the field should fill accordingly like as i put Mumbai, the names n Ph nos come accordingly ie. AMD_NAME=ANIK BHASIN AND ADM_PH = 9921356982 and the same for GM name as well. regards, Deepak rawat --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~--- Book1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Leading zeros
Hi John, Use ' before 0 os it will display number as '0985542. Rgds, MB On Tue, May 19, 2009 at 7:21 PM, john johnbah...@hotmail.com wrote: I have a basi questions. When I try to enter a number with a leading zero, it ignores the zero and enters the rest. Where do I go to correct this so all numbers including the leading zero be entered and displayed? -- With Love, Mahesh Bisht --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Fwd: VBA help needed
Hello Harmeet, I think I have a solution for your problem. see attached file. Regards, Sergio 2009/5/18 Harmeet Singh harmeet.hew...@gmail.com -- Forwarded message -- From: Nihar Turakhia turakhia.ni...@gmail.com Date: Mon, May 18, 2009 at 12:47 AM Subject: VBA help needed To: harmeet.hew...@gmail.com Harmeet, I've just started learning vba and stuck rightnow with something. Any help will be greatly appreciated. I receive the daily settlement data from external source for different commodity products in excel So I was thinking if I can write a macro which would grab that settlement data from that cell for each day and put in new row. ( so all the data would be in one column ) So there will be a settlement data for 4/1/2009 in cell B1 Tomorrow when i open my book, new settlement data for 4/2/2009 in cell B2 and next day as 4/3/2009 in cell B3 Only thing I would like to avoid is that if I open the workbook twice in a same day the macro should not populate new cell instead overwrite the previous cell which has todays date in it. -- Thanks Regards, Harmeet Singh --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~--- Dateadder.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Check date if exist break hyperlink
please attach the file. 2009/5/19 AYalcin ahmetyalc...@gmail.com Hello Everyone, I am very new in macro programming and would appreciate any help I can get. I have a workbook with two sheets. The first sheet has mm/ labeled hyperlinks eg 03/2008, 04/2008.. 04/2009. When I press on the hyperlink the macro which I have writtien in a very primitive way opens two other files and retrievs the related cells into my second sheet, creating a database. What I further wish to do is to be able to check if the date on the hyperlink exist in my related database column and if true break hyperlink. I thank you in advance. Regards Ahmet --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro or add-in to Highlight Current Row in Color in any spreadsheet I want
Dear OldRider, You can record a blank macro and paste the code there. Or you can go to VB editor and insert a module there. Then you can use the code. for Example: Sub ColorRow() ActiveCell.EntireRow.Interior.Color = RGB(255, 255, 160) ActiveCell.EntireRow.Font.Bold = True ' Check for first execution of the macro and set row ' value if it is: If x = Empty Then x = ActiveCell.Row ' Set previous row property back ElseIf Not x = ActiveCell.Row Then Rows(x).EntireRow.Interior.Color = RGB(255, 255, 255) If x 1 Then Rows(x).EntireRow.Font.Bold = False Rows(x).EntireRow.Interior.Pattern = -4142 End If ' Capture new row value for comparison against next selection. x = ActiveCell.Row End Sub Then if you try to add a macro button to the tool bar or whereever you want, You will find a macro named ColorRow for the example given above. This will enable you to use the macro for all the worksheets. Please let me know if this helps. Regards, Andy On Wed, May 20, 2009 at 5:37 PM, OldRider sowhat...@gmail.com wrote: Folks: G'day. I am an absolute novice at VBA and macros. I do get around by adapting solutions provided by more knowledgeable and learned people. So please be understanding. This is the issue I am trying to resolve. I need to highlight the current row I am using and I have the perfect code to use. The catch is that I would like to have it as a button (little smiley face) on the toolbar so that I can use it on any spreadsheet I am creating/using. I do not intend to distribute the code. I have found the perfect code - Thanks to David McRitchie. (http:// www.mvps.org/dmcritchie/excel/event.htm) This is the code I used: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.colorindex = 0 'Turn off previous use If Cells(1, 1) = . Then Exit Sub Target.EntireRow.Interior.colorindex = 38 End Sub However as this code is limited to the sheet in which the code is placed, I then adapted it so that it is available on all worksheets in a workbook. Thanks to Damon Ostrander.(http://www.mrexcel.com/archive/ VBA/29582.html) I pasted a WorkbookSheetSelectionChange event in the ThisWorkbook event code pane and the code now works perfectly on all worksheets in a workbook. But I have to paste it to every workbook that I need to use the code in. With my limited knowledge, I then tried to turn it into an add-in ( .xla file) following instructions I found on the net 1.Insert Module 2. Pasted unchanged code into module 3. ALT-Q to return to sheet and saved as .xla files 4. Restarted Excel and ticked the add-in under Tools. 5. Tried to customise button - Toolbars - customise - command-macro- moved Smiley face to toolbar - Assign macro.No macro available Please help!!! I am not competent enough to build a toolbar or write additional code to create a button. I would like to be able to use this code at my discretion on any spreadsheet without having to paste the code every time into the current spreadsheet. Thanks in advance and God bless, OR --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: more than 3 rules in condtional formatting
Try the link below: http://www.mrexcel.com/archive/Formatting/12054.html Norman On Tue, May 19, 2009 at 1:50 PM, Balla kishore.maro...@gmail.com wrote: Hi, M using excel 2003, and say i need to have this below rule for a cell if value is india, background color blue if value is aus, background color yellow if value is sa, background color pink if value is eng, background color red if value is sl, background color navyblue m using conditional formatting, but it gives only 3 rules, is there any workaround for this Thanks Kishore --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Macro or add-in to Highlight Current Row in Color in any spreadsheet I want
There are several ways of doing this, using a macro in different sheets - u can assign macro to a shortcut, add as a button to the toolbar etc., Check the below article, it will explain you how to assign macros to shortcut keys, buttons and objects. http://funwithexcel.blogspot.com/2009/05/macro-basics-assigning-macros-to.html Hope this is helpful. On Wed, May 20, 2009 at 6:36 AM, Aindril De aind...@gmail.com wrote: Dear OldRider, You can record a blank macro and paste the code there. Or you can go to VB editor and insert a module there. Then you can use the code. for Example: Sub ColorRow() ActiveCell.EntireRow.Interior.Color = RGB(255, 255, 160) ActiveCell.EntireRow.Font.Bold = True ' Check for first execution of the macro and set row ' value if it is: If x = Empty Then x = ActiveCell.Row ' Set previous row property back ElseIf Not x = ActiveCell.Row Then Rows(x).EntireRow.Interior.Color = RGB(255, 255, 255) If x 1 Then Rows(x).EntireRow.Font.Bold = False Rows(x).EntireRow.Interior.Pattern = -4142 End If ' Capture new row value for comparison against next selection. x = ActiveCell.Row End Sub Then if you try to add a macro button to the tool bar or whereever you want, You will find a macro named ColorRow for the example given above. This will enable you to use the macro for all the worksheets. Please let me know if this helps. Regards, Andy On Wed, May 20, 2009 at 5:37 PM, OldRider sowhat...@gmail.com wrote: Folks: G'day. I am an absolute novice at VBA and macros. I do get around by adapting solutions provided by more knowledgeable and learned people. So please be understanding. This is the issue I am trying to resolve. I need to highlight the current row I am using and I have the perfect code to use. The catch is that I would like to have it as a button (little smiley face) on the toolbar so that I can use it on any spreadsheet I am creating/using. I do not intend to distribute the code. I have found the perfect code - Thanks to David McRitchie. (http:// www.mvps.org/dmcritchie/excel/event.htm) This is the code I used: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Cells.Interior.colorindex = 0 'Turn off previous use If Cells(1, 1) = . Then Exit Sub Target.EntireRow.Interior.colorindex = 38 End Sub However as this code is limited to the sheet in which the code is placed, I then adapted it so that it is available on all worksheets in a workbook. Thanks to Damon Ostrander.(http://www.mrexcel.com/archive/ VBA/29582.html http://www.mrexcel.com/archive/VBA/29582.html) I pasted a WorkbookSheetSelectionChange event in the ThisWorkbook event code pane and the code now works perfectly on all worksheets in a workbook. But I have to paste it to every workbook that I need to use the code in. With my limited knowledge, I then tried to turn it into an add-in ( .xla file) following instructions I found on the net 1.Insert Module 2. Pasted unchanged code into module 3. ALT-Q to return to sheet and saved as .xla files 4. Restarted Excel and ticked the add-in under Tools. 5. Tried to customise button - Toolbars - customise - command-macro- moved Smiley face to toolbar - Assign macro.No macro available Please help!!! I am not competent enough to build a toolbar or write additional code to create a button. I would like to be able to use this code at my discretion on any spreadsheet without having to paste the code every time into the current spreadsheet. Thanks in advance and God bless, OR -- http://funwithexcel.blogspot.com/ --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: SQL 2005
excellent thank you very much On 19 mayo, 12:26, Fabio Lemos flnle...@gmail.com wrote: You have to enclose your parameter in ': see below; SELECT user_code FROM ousr WHERE user_code= ' busca ' 2009/5/19 Erikito1981 erikito1...@gmail.com Hello everyone I have a problem, I have this code which works fine me if I use is the field number but if the data is character sends me an error message This is the code busca = TextBox1.Text Set Archivo = New ADODB.Recordset Sqlc = Driver={SQL Server};Server=SAPSERVER;Database=INTEGRADORA;Uid=sa;Pwd=B1Admin; Cnn.Open Sqlc Sql = SELECT user_code FROM ousr WHERE user_code= busca Error en Timepo de ejecucion -2147217900(80040-e14) invalid Column name '1234' Value 1234 is the value of the variable busca Thanks. -- Fabio L Lemos email: flnle...@gmail.com --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: passwordupprotect file
Still it asks for a password On May 20, 11:05 am, H Upadhyay harishcupadh...@gmail.com wrote: Hi Tariq Passwored.xla crack password, but there is some way that we know what is the password. Regards Harish Upadhyay On 5/18/09, tariq tariq.78...@gmail.com wrote: hello mahesh, this file password is : AABBBAABBAAP AND ANY PASSWARD CRACKING SO MAIL ME ... my e_mail : tariq.78...@gmail.com contect no. : 09893729520 tariq khan +91 9893729520 --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: more than 3 rules in condtional formatting
thanks for link, but i want a way out without using macros On May 20, 6:48 pm, Norman May nor...@gmail.com wrote: Try the link below: http://www.mrexcel.com/archive/Formatting/12054.html Norman On Tue, May 19, 2009 at 1:50 PM, Balla kishore.maro...@gmail.com wrote: Hi, M using excel 2003, and say i need to have this below rule for a cell if value is india, background color blue if value is aus, background color yellow if value is sa, background color pink if value is eng, background color red if value is sl, background color navyblue m using conditional formatting, but it gives only 3 rules, is there any workaround for this Thanks Kishore --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Chart Formatting in Excel 2007
Hi all, new to the group and fairly new to VBA programming. Here is my question: I'm writing a Macro to automate chart formatting tasks in Excel 2007. I noticed that the macro recorder does not record all of my steps and I can't figure out which commands to use from the object browser so hopefully somebody here can help me. My chart is a 3D contour plot. I'm trying to specify which colors are associated with certain numerical ranges in the plot and the legend. Manually, I accomplish this by selecting the individual legend entry, right clicking and selecting format band and then selecting the solid fill option with my preferred color. So far I have a hunch that the right code might go something like this: ActiveChart.Legend.LegendEntries(1).Fill.ForeColor.RGB = RGB(128, 0, 0) However, I get an error when I try to run this. Thoughts? Suggestions? Ridicule? Thanks, Dave --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ rows property in VBA for excel Macro
How can I change the code below so that VBA deletes any rows in the current region where th value of the first cell is blank? For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.Delete last = this Next thanks for your feedback --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---
$$Excel-Macros$$ Help in Excel - How can we work in shared file while running the macro?
Hi, I required a urgent help, I had an excel file which i want to work in shared, but in that file I am using macro with VB codes, I want help how to run the macro while working in shared file using by more than 1 person at the same time. Please find the attached file for your reference. Any help on this will be really appreciated. Regards, Ankur --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~--- Book1.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Chart Formatting in Excel 2007
On Thu, May 21, 2009 at 2:41 AM, Dave dtordon...@gmail.com wrote: Hi all, new to the group and fairly new to VBA programming. Here is my question: I'm writing a Macro to automate chart formatting tasks in Excel 2007. I noticed that the macro recorder does not record all of my steps and I can't figure out which commands to use from the object browser so hopefully somebody here can help me. My chart is a 3D contour plot. I'm trying to specify which colors are associated with certain numerical ranges in the plot and the legend. Manually, I accomplish this by selecting the individual legend entry, right clicking and selecting format band and then selecting the solid fill option with my preferred color. So far I have a hunch that the right code might go something like this: ActiveChart.Legend.LegendEntries(1).Fill.ForeColor.RGB = RGB(128, 0, 0) However, I get an error when I try to run this. Thoughts? Suggestions? Ridicule? Thanks, Dave -- Satish Mandava Financial Planning Consultant http://investoreducation.blogspot.com --~--~-~--~~~---~--~~ - Some important links for excel users: 1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at http://www.excelitems.com 2. Excel tutorials at http://www.excel-macros.blogspot.com 3. Learn VBA Macros at http://www.vbamacros.blogspot.com 4. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com If you find any spam message in the group, please send an email to: Ayush Jain @ jainayus...@gmail.com or Ashish Jain @ 26may.1...@gmail.com - -~--~~~~--~~--~--~---