Is there any way to modify the 1st style under the Light category? I like it because it's the most simple but it won't allow me to Modify or Duplicate. Is there a work around to duplicating/copying it? I've tried manually creating a new style but I can't get it to get the lines fills correctly.
View 2 RepliesI have a data table and want to create six charts. In theory pivotcharts would be the way to go. However every time I refresh the charts they revert to excel's default chart and all my colour scheme and nice layouts are lost.
View 3 Replies View RelatedI have tried to reset the cell reference style back to A1 from R1C1 (Tools-->Options-->General tab-->uncheck R1C1 reference style), but when I close Excel and reopen it, the reference style changes back to R1C1 style. When I make the change I described above using the Options feature, it changes it for the current file and saves it with the file. When creating a new file in the same session, it keeps the A1 reference style, but when I close Excel and reopen it, it returns to the R1C1 style. Any ideas about how to return to the default A1 reference style for the next Excel session?
How to set macro shortcut for toolbar items which do not have a shortcut already like the "," style button which converts 12000 to 12,000
View 6 Replies View RelatedI have a table that has, amongst many columns, a year, month and a name column.
I want to do a formula that takes in 3 values (name, year, month) and returns a sum of values found in a variety of other columnswhere these 3 values are matched.
I would know how to do this in SQL but wonder if Excel can do this type of thing on one large data table?
Is there anyway to select all cells with a particular style in a worksheet? Like a "Go To" (F5) or Find Styles equivalent?
View 2 Replies View RelatedI have this code to copy excel data to a word document:
Code:
Set wrdRange = wrdDoc.Range
With wrdRange
.Collapse Direction:=wdCollapseEnd
.InsertParagraphAfter
xText = Rng.Cells(i + 1, 1).Value
.InsertAfter xText
.Collapse Direction:=wdCollapseEnd
End With
I need to apply a heading style to xText after inserting. xText can contain many words.
I have a spreadsheet wherein the defined data input cells are formatted using styles, i.e. data input style. I would like to know how to select all cells formatted with that style within a spreadsheet (whether worksheets are visible, hidden or very hidden) and delete the contents of those cells while still keeping the cell style.
View 9 Replies View RelatedI want to know if there is any formula to match two numbers which have same digits but are in different style. I have attached a spreadsheet with numbers in column A and Column B. Both columns have the same number just the style is different, is it possible that i get a match based on the digits match and then return the output as correct or wrong?
If all digits match from column A with column B it should show as correct else wrong.
i have quite a lot of excel 2003 spreadsheets which once edited and saved by excel2007. now, i'm using back excel 2003. (because my efficiency dropped by half by searching the command i wanted in excel2007.) when i open them with excel2003 again, there are a lot of styles. i want to remove them. but it is a tedious job to remove them one by one for these spreadsheets.
View 3 Replies View RelatedHow can I concatenate 2 cells address style? I'm trying to merge 2 cells into one, so that one is under the other in the same cell.
View 5 Replies View RelatedI have an excel that gets updated monthly with new sales data. The sales data comes straight from our custom CRM system in a .xlsx file. All columns on this spreadsheet have a column type of general.
In the excel that is updated monhtly however their are certain columns that are set to a date type so that we can group pivot tables by date. Currently I have to paste the data that is exported from our CRM system into the monthly spreadsheet (beneath all the prior months data), create a new column next to any date field and then use the formula
Formula:
[Code] .....
I then have to take the resulting data and paste them as values over the original data so that it is in the correct format.
Is there a quicker way of doing this so I don't have to keep adding a new column and doing the forumula, can I somehow set the column so it automatically converts data in a general format to a date format.
I have been trying to get a back button to work on an 2003 excel document that works like a website.
In the web toolbar there is a facility to go back and forward (very much like using internet explorer). I want a button on the actual sheet that does this command. If i record a macro and click the web 'back' button it records the end result of the process, not the process itself -
eg if sheet 1 and sheet 2 link to sheet 3 and I click a hyperlink from sheet 1 to sheet 3 then create a back button on sheet 3 and record a macro by clicking on the web toolbar back button the macro records the end result - eg Application.Goto Reference:="Sheet1!R14C8". However if i then go to sheet 3 through sheet 2, the back button I created takes me to sheet 1 not back to sheet 2.
Anyone know the code I could use to make it actually go 'back' to the previous page?
we in india to any number put comma as stated below
ie 15,13,10,565.00
fifteen crores thirteen lacs ten thousand five hundred sixty five only.
00,00,00,000.00
but excel we have only thousand separator.it separates the number after every thousand.
can it possible in excel to formate number as per our style.
00,00,00,000.00
Every time I copy and paste cells in a specific workbook it changes the reference style to R1C1. Setting it back in the options, saving the document, closing Excel then reopening brings it back as A1 referencing but copy and paste will again return it to R1C1.
I've checked for VB code and there's nothing there either.
Is there a macro that can select all cells with a certain style and then clear the values. The idea is that a big document has cells that are formatted as "entry-fields" and others that are not. I want to be able to select all of them with a macro (they are everywhere) and clear them of their contents (hence a reset button for this style).
I've tried something with If Target.Style = "entry-field" Then but i don't know how to search for them and have them in Target or a variable in the first place.
I recorded this and I was wondering if there was a more simple way to add a thin style border to my cells?
'Insert BorderUnion(Range("K6", Cells(LastRow, "K")),
Range("B6", Cells(LastRow, "B"))).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
[Code] ......
After hours of searching the web and not finding what I am looking for, I am starting to doubt whether or not this is possible. Here is what I am trying to do.
I have about 10 form buttons on top of one of my worksheets. Each one is attached to a macro that filters 10 pivot charts. All the macros work just fine, but it starts to get confusing what data is being represented. So, what I would like to do is have each button set to display as depressed (or similar) when its pushed using vba in each macro. That way, there will be no confusion as to what buttons (filters) people have pushed.
When I put on any cell eg:
aaaa@bbbb.com
or
www.dededede.com
excel automatically applies the Hyperlink style formatting to the cell. is there a way to avoid this, other than using: [HTML]Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Hyperlinks.Delete
End Sub[/HTML]
After all the work and calculations applied to data from various macros, I would like to have a final macro simply insert a title row, always the top row with various titles. Theres 11 columns, thus it would be great for the code to simply insert the row with predefined bolded titles, such as T1, T2, T3....T11. These 11 cells would than share then same bolded bottom line, making it visibly separated from the rest of the rows. I know this is easy, and Ive looked throughout the forum, but I can find things close, but not close enough.
View 2 Replies View RelatedI'm just finishing up a project involving migrating an old Excel 4 macro to VBA. Unfortunately, now that my spiffy (and slow) VBA is finished, I can't find a way to delete the Excel 4 macro! I've tried going to Tools -> Macro -> Macros, and while the old macros are shown there, the delete button is greyed out!
View 3 Replies View RelatedI'm wanting to create a simple form type interface for the viewing and changing of data one record at a time. What I'd like is on sheet one, and simple portait form to display
First Name
Surname
House Name
Address Line 1
Address Line 2
Town
County
on sheet two each column will be one of the above fields and each row will be a new record. Is there a way to do this in excel? Im trying to create a simple user experience that hide the rows & colums.
I have a spreadsheet with a content in cells. Some of the cells are indented left and some aren't. I would like a macro that groups all of the cells that are indented left above the cells that are not indented.
I've attached a spreadsheet : Book1.xlsx
I'm putting together a calendar that will have show when people are off on holiday, training, out of the office or ill. But as it will identify people by name I have changed to cell style to reflect just what activity they are doing. As such I want to be able to count over a year just who has done what.
Is there a piece of code that can count based on the text inside a cell and it's style e.g. "20% - Accent1"
From the keyboard, if you select a chart with data labels, right click the data labels then "reset the data lables to match style", it works fine; but there appears not to be a programmatic equivalent to accomplishing this same task... at least the macro recorder does not shed any light on the subject. Is there code available to do the same thing?
View 3 Replies View RelatedI have a macro that creates an email based on the contents of each column in the worksheet. The macro works great, but I would like to format a column (A) which contains numbers into the Comma Style, prior to sending out the email.
Is there a VBA code that will format a given column (A) into a certain style (Currency, Comma, Percentage, etc) ?
I'm developing a user interface for my spreadsheet that allows users to choose from pre-set skins. The primary mechanism for setting the skins is through a fairly long list of styles applied throughout several sheets. By choosing a skin, a user will start a code that will cross- reference the background color, font characteristics, etc. for that particular skin, and those characteristics will be applied to the pre-set styles. Cool in theory. Problem: The VBA code for modifying the styles doesn't seem to apply consistently. Particularly any code involved in fonts or borders yields an error: "Unable to set the XXXX property of the YYYYY class." This despite it all being modeled on a record macro session output. What setting or element am I missing? I would be most grateful for your expert input. Here's the module that sets the styles based on a table of style names in the Design sheet. I've attempted to keep the myriad of settings clear by naming the ranges by their properties. Each of the lines causing errors are commented out.
Public Sub SetStyles()
'Works from the Style Table in the Design Sheet -- Assigns values based on table contents.
Dim i As Integer
Dim j As Integer
Dim oStyle As Object
Dim sStyle As String
Dim xlVAlign1 As XlVAlign
'Can't delete all existing styles because it deletes all style refs in sheets.
Call UnprotectUs 'Unprotects all sheets so that styles can be modified.
'For Each oStyle In ActiveWorkbook.Styles
' If Not oStyle.Name = "Normal" Then oStyle.Delete
'Next
'Add the styles that are named and don't exist.
i = 1
For Each oStyle In Range("StyleName").........................
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................
I am still in the process of creating my Invoice, and now I want to add a proper save macro. I want to add a Macro button, which when clicked it copies that invoice sheet as it is and then places it at the end of the sheet tabs, so they're saved after the first 2 important parts of the invoice, and then saved in time order. If possible, i'd also like it to rename the invoice tab to the Customer's name/lastname IF Possible. I've attached the file so if someone fancys trying to do it for me, they can. I want these saved sheets to be seperate for each invoice/customer, and I want them to be in the same file as the invoice 'creator' it's self.
View 6 Replies View RelatedHow can I replace the border style with a macro across my entire data?
I would have assumed something like this would work:
[Code] .......