$$Excel-Macros$$ Re: Store Cell formatting
Hemant, I was hoping someone would come up with an answer for you! I have an application that stores the entire workbook as data in Oracle tables. the actual document is retrieved and re-assembled. If changes are made, the Oracle database is updated. Now, users sometimes want to make cells bold or change the color or font and I wanted to be able to store the formatting in the database. I ended up creating an array and stored the characteristics. then, I created a delimeted string like: textstring|justification|bold|fontcnt|font1|length1|font2|length2| then stored the string. when I retrieved the record, I used split() to re-create the format array. It would be nice to be able to save the range object as an array, but I haven't found a way to do that. Sorry. Paul On Nov 10, 1:03 am, RolfJ r...@pacificsound.us wrote: Please explain why you don't save the worksheet resulting from your calculations in the intrinsic Excel format (i.e. as an Excel workbook). On Nov 8, 12:20 pm, Hemant Hegde hemantbales...@gmail.com wrote: Hi After a lot of coding and lengthy calculations (takes up to 2 minutes) I get a sheet (actually a report) with lot of numbers and different cell formatting. Now I need to store the cell values together with cell formatting in a binary file to avoid repeated lengthy calculations. The only option I able to think is to store all the formatting values one after another in the binary file eg. For cell's background colour, I will have to store the value of cells(1,1).interior.colorindex as a number and write it to the binary file. While opening the binary file, it has to read it and apply it back to the cell. Any better Idea? Im sorry if I failed to explain correctly what i want to do! Can I get all the formatting of a cell as a single number or a string by any means? Professional programmers help me please -- Hemant Hegde- Hide quoted text - - Show quoted text - --~--~-~--~~~---~--~~ -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: procdure to limit the printing to fix number of times
ASSUMING that column A ALWAYS has data, then you could use: '--- Private Sub Worksheet_Change(ByVal Target As Range) If (Application.WorksheetFunction.CountA(Range(A:A)) = 10) Then MsgBox You cannot go beyond this Chr(13) Workbook will close. ThisWorkbook.Close savechanges:=False End If End Sub '- Note: This macro must go into the SHEET module (not a standard module) the easiest way to open the sheet module is to right click on the sheet name and select view code Paul From: OSAVentures Calamba osav...@gmail.com To: excel-macros@googlegroups.com Sent: Mon, November 9, 2009 8:23:57 PM Subject: $$Excel-Macros$$ procdure to limit the printing to fix number of times dear gurus, say i have a worksheet, but i want to limit the printing of this worksheet to 10 times only. is it possible? any vba idea. most probably this is a worksheet print event? im new to macro. thanks for understanding. dellosa --~--~-~--~~~---~--~~ -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Need help in Pivot table
On Sun, Nov 8, 2009 at 9:11 PM, Dilip Pandey dilipan...@gmail.com wrote: Dear Mahesh, Attachment is solved as per your requirement. Thanks. -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On 10/29/09, Mahesh mahender.bi...@gmail.com wrote: Dear Frineds, Please help me to solve the problem in attached sheet. -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- Copy of Copy of Xl000.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Conditional sum problem ( first ns and equal to certain values)
Dear Dilip, Again many thanks for your answer, it helped my a lot to solve my problem. Apologies, in first I tried to generalize too much, and I wasn't very clear ( and my bad english doesn't help) By the way, even if your syntax helped my, I can't managed to get your formula working Maybe if you have a look at my workbook you can have a clearer idea http://groups.google.com/group/excel-macros/web/work2.xls?hl=it -In the worksheet page there is the original database -In the worksheet My Solution in column D there is a sumproduct that gives my the position of the field I need In G1 there is my formula. Does it exist a way to have the sumproduct formula in column d incorporated in the formula in G1? I tried with Array format but didn't worked Many thanks for your kindness, Antonio. On 9 Nov, 12:23, Dilip Pandey dilipan...@gmail.com wrote: Dear Tony, Earlier you wanted the sum of largest 3 labels which are greater than Zero and your previous email also confirms the same, reproduced belowP- ** *I have to find the sum only of the first 3 A that are greater than zero* Now as you have changed the requirement and need sum of 14 values, I have customized the solution and attached herewith. Best Regards, -- DILIP KUMAR PANDEY MBA-HR,B COM(Hons.),BCA Mobile: +91 9810929744 dilipan...@gmail.com dilipan...@yahoo.com New Delhi - 110062 On Mon, Nov 9, 2009 at 2:41 PM, Tony from Work xerses...@gmail.com wrote: First of all many thanks to Dilip and Deepak for the answers!!! Sadly they don't solve my problem, what I exactly need is one formula that sum the value only of the first 14th values, and ignore any further value. This is a sample of my worksheet: A B C Time Keys Amount 20-10-09 19:05 L 0.00 20-10-09 19:25 L 0.00 20-10-09 20:00 SH 0.08 21-10-09 01:15 L 0.00 21-10-09 01:20 L 0.00 21-10-09 02:00 L 0.00 21-10-09 03:35 SH 0.08 21-10-09 03:55 L 0.00 21-10-09 04:30 ST 0.00 21-10-09 08:00 L 0.00 21-10-09 08:25 SH 0.17 Now I've added after the Amount column a counting column with a simple sumif ( =COUNTIF($B$2:B2,SH) ) that gives me rank of each SH. Then I've used a sumproduct ( =SUMPRODUCT((C2:C780)*(B2:B78=SH)* (D2:D78=14)*(C2:C78)) ) that: - Check if the value in C column is greater than 0 - Check if the value in B column is equal to SH - Check if the value in D Column is less or equal to 14 And if everything is true gives me the sum. I know that my solution even if works is a bit stupid, And I'm puzzling myself if there is something that could work without adding a colum in the worksheet Many thanks, Antonio. Conditional Sum by DILipandey-revised.xls 26KVisualizzaScarica --~--~-~--~~~---~--~~ -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Pasting a block of cells/object
Hi, I would like to know if this is possible in excel 2007 and any pointers where to look. - I have a block of cells (like a sort of table) which I want to define as one object (is there any way I can do this) - Is it possible somehow with a single function to copy this object (or block of cells) say 30 times? instead of copy/paste each one? It would be good if I could put a number in a cell and then run some sort of function that would copy this x number of times. 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: InputBox
Hi Tommy Where did you get the code name.copy ? It doesnt work that way. Its very simple just write Range(a1)=name when you use = operator, the value on the right hand side is assigned to the variable on left side 2009/11/9 Tommy jntwh...@verizon.net I need to be able to insert text into a cell using the “inputbox” function linked to a button. The macro below allows me to input the text, but getting it to go somewhere eludes me. I realize that Copy.Name doesn't work but how do i direct the entered text to a cell? Suggestions appreciated. Tommy Sub New_Name() Name = Application.InputBox(Enter New Name) Copy.Name Range(A1).Select ActiveSheet.Paste End Sub -- Hemant Hegde --~--~-~--~~~---~--~~ -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~---
$$Excel-Macros$$ Re: Excel VBA dynamically fill ComboBox?
Hi, I am attaching a file which is based on the same logic. U can use the same logic for your requirement. Hope this will help u. Regards, Deepak Rai On Tue, Nov 10, 2009 at 12:11 PM, RolfJ r...@pacificsound.us wrote: Assuming that your country column is column A, place the following code into the VBA section of the form containing your three ComboBoxes (referred to as cmbBoxCountry, cmbBoxState and cmbBoxProduct) and give it a try: Private Sub cmbBoxCountry_Change() Call UpdateComboBox(B, cmbBoxState, cmbBoxCountry) End Sub Private Sub cmbBoxState_Change() Call UpdateComboBox(C, cmbBoxProduct, cmbBoxState) End Sub Private Sub UserForm_Activate() Call UpdateComboBox(A, cmbBoxCountry) End Sub Private Sub UpdateComboBox(sourceCol As String, ByRef c As MSForms.ComboBox, Optional cRef As MSForms.ComboBox) c.Clear Dim r As Range Set r = Range(Range(sourceCol 2), Range(sourceCol Rows.Count).End(xlUp)) Dim rCell As Range If cRef Is Nothing Then For Each rCell In r.Cells If Not IsInComboBox(rCell.Value, c) Then c.AddItem (rCell.Value) Next rCell Else For Each rCell In r.Cells If rCell.Offset(, -1).Value = cRef Then If Not IsInComboBox(rCell.Value, c) Then c.AddItem (rCell.Value) End If Next rCell End If If c.ListCount 0 Then c.ListIndex = 0 End Sub Private Function IsInComboBox(s As String, c As MSForms.ComboBox) As Boolean Dim i As Integer For i = 0 To c.ListCount - 1 If c.List(i) = s Then IsInComboBox = True Exit Function End If Next i IsInComboBox = False End Function Hope this helped, Rolf Jaeger SoarentComputing http://soarentcomputing.com/SoarentComputing/ExcelSolutions.htm On Nov 8, 7:47 am, Meimei xxu8810...@gmail.com wrote: Hello, I have a question on using VBA to programme several ComboBoxes whose items are dynamically filled? I have a worksheet containing country names, states, and product, such as CountryState Product 1 US MA Banana 2 US MA Orange 3 US WV Apple 4 US WV Apple 5 US CA Banana 6 Canada Quebec Orange 7 Canada Quebec Orange 8 Canada Albert Apple 9 Canada Albert Banana I want to the 1st ComboBox to read the list of country names and filled by the unique country names, in this, I have two country names, US and Canada. After the 1st ComboBox is selected, I want the 2nd ComboBox to be dynamically filled with the appropriate state/province names, i.e., if the user chooses US, then the 2nd ComboBox only have the choices of MA, WV, and CA. Similarly for the 3rd ComboBox, if the user chooses US and then WV, the 3rd ComboBox should list Apple. So I figure that the code should dynamically search for unique country names, state names, and product names, and also be able to link the appropriate list of states to country, and appropriate list of product to state or provinces. But I don't have a clear idea how to do this. Can someone help me with some sample codes? Thanks a lot! -- Thanks, Deepak Rai --~--~-~--~~~---~--~~ -- 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 HELP US GROW !! We reach over 6,500 subscribers worldwide and receive many nice notes about the learning and support from the group. Our goal is to have 10,000 subscribers by the end of 2009. Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe -~--~~~~--~~--~--~--- Concatenate(1).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
