Free Microsoft Excel 2013 Quick Reference

VBA to change text on two slide masters

Hi folks,

I'm working on a power point presentation that has two slide masters and I want to change the footer on them both. My code currently reads....

Code:
    
ActiveWindow.ViewType = ppViewSlideMaster
    ActivePresentation.SlideMaster.Shapes("Rectangle 4").Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Select
    ActiveWindow.Selection.ShapeRange.TextFrame.TextRange.Characters(Start:=1, Length:=17).Select
    ActiveWindow.Selection.TextRange.Text = "Type1"
Problem is I now want to switch to the other slide master and have it change the text in what is "Rectangle 4" there as well. I tried returning to a regular view, and switching to slide using the second master and repeating but it keeps changing only the first slide master. Is there a slidemaster equivalent to

Code:
Thanks and have a great weekend.


I am trying to change text on my spreadsheet from all CAPS to Title Case

I'm building an Excel 2007 workbook that contains multiple pivot tables which are all based on same pivot cache. The data source is external and retrieved by a SQL query from an Access 2007 DB. The SQL query may be changed for a purpose so a new pivot cache is created when it's modified.

I can use Pivot Table Wizard to manually steer all pivot tables to new master copy of pivot table without a problem. But I want to use VBA code to do the process to same time as I have more then 10 pvt in the workbook. So I use the Excel's macro to record a manual change. Below is what is the VBA code produced by Macro:
    Range("E77").Select
    ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
        "pvtMstrCopy"
    ActiveWorkbook.ShowPivotTableFieldList = True
pvtMstrCopy is the name I give to the master pivot table. Cell E77 is inside the target pivot table which I want to change data source.

But the problem is I can't even re-run this recorded macro. Excel report error message Run-time error '1004' PivotTableWizard method of Worksheet class failed I add a sheet name to SourceData parameter to "Sheet1!pvtMstrCopy" and it still has the same error.

It's frustrating since I've been searching same error in various Excel forums for 2 days and I didn't find same one.

Can anyone tell me how to use VBA to change pivot table data source to another one? Thanks in advance.

Does anyone have any suggestions on how to change text into number?
For example, in cell A1, the value is "13.555", and I would like to display
13.555 in cell B1 as a number.
Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Hi again,
I have developed a workbook that other people use on their machines, and
when any modifications are to be made I send out a separate workbook
containing VBA code to do the changes. The problem I have is that I cant
change any of the code within the remote workbooks.
Is there a way I can use vba to change vba?
Any help greatly appreciated
John

Hi there

is there a way to use VBA to insert text into text box? I don't believe it is possible to put a formula into a text box (I have posted a question on the Excel general forum to check this), so my next thought is: can I use VBA to insert the values I want instead?

Basically, I have a text box which I want to display certain text based on certain criteria.

Thanks in advance
Sunil

Can I use a macro to change text in a cell?
As an example, I have this list of names in a column. I'd like every other name to have a semicolon instead of a comma after the name.

My list has commas now
Tommy,
Joe,
Warren.
Billy,
Bob,
George,

And I want it to look like this.
Tommy,
Joe;
Warren.
Billy;
Bob,
George;

Since I'm using macros on this page, I'd like to use a macro to do this, but any ideas would help.

Thanks

I need to know how to use vba to change the caption in a command button on an
excel spreadsheet. Any assistance would be appreciated.

Paula

Visual Basic for Applications (VBA) to change UserForms in ...
It includes examples and Microsoft Visual Basic for Applications (VBA) macros that show you how ... 207714 (http://support.microsoft.com/kb/207714/) XL2000: Run-time errors using ...

How to use Visual Basic for Applications (VBA) to change UserForms in ...
How to use Visual Basic for Applications (VBA) to change UserForms in Excel 2003, Excel 2002, and Excel 2000 ... Run-time errors using UserForms collection

I need a formular to change data on a certain date every month so that on the
1st of every month a cell value will decrease. say from 48 to 47 and so on.
Any feedback would be greatfull. Thanks

How to change text in first row or first column?
I don't like excel default style to show first fixed row as A, B, C... and first column as 1, 2, 3...
How can I change them or hide them? I have seen a sample of that, but don't know how to realize it.

How can I use VBA to change VBA code in another excel file?

Thanks,

Keith

Dear all,

I would like the background of cells I13 to I29 to change if:
1) they are empty AND
2) the 1st digit of their corresponding cell in column A is less than 3.

Could someone please give me a code for this. I have tried to use conditional formatting but I cannot get both conditions together.

Could someone help please.

PS. I do not mind what colour the cell becomes as I guess it will be defined by a number which I can change if necessary.

Thank you in advance for your help.

Regards,
Cdmg

Hey guys, I'm certain there's a post on here somewhere about how to do this, I'm just having trouble finding them through my search inputs.

This is what I need to do in VBA for a formula

I have a user text box called INPUT
for this text box the user inputs a number either 1,2,3,4,5

If the user inputs 1, I need VBA to change variable x to
x = something

eg/
if user inputs 2: x = 7
if user inputs 3: x = 10
etc. etc.

the value of x is a set number based on the INPUT chosen
eg/ input 2 must equal 7, and variable 3 will equal 10

What's the simplest way to set the variable value from a user inputted number while the formula is being calculated (with cmd_click)?

Thanks for the help

-Timothy

Hi Guys,

Its probably something silly, but I am working in excel 2007 and I have some pretty buttons that I grabbed from the ready made shapes. I added some text (these pre-made shapes seem to already have a text box imbedded somehow) and now I have written this code that is supposed to grab my template button, copy it, rename its object name, change the text displayed on it and reposition it. The bug is occurring on the line ".TextRange.Text = "New Button""...any suggestions?

Set othshape = myDocument.Shapes.Range("Template1")
othshape.Select
Selection.Copy
ActiveSheet.paste

With othshape
.Name = "New" & i
.TextRange.Text = "New Button"
.Top = l + 30 + (i - j) * 18
.Left = m + 3
End With

Next

Thanks so much!

Hi I have 4 rectangle with pictures as fill and I would like to change them with VBA to a grey / white fill. I simply did it with record macro, it worked a few times and then, not anymore. The error msg is: Runtime Error: The specified value is out of range. IT sometimes get stuck on the line: ForeColor.ObjectThemeColor = msoThemeColorBackground1
, sometimes on .ForeColor.Brightness = -0.0500000007. Also, I never like to use .select but it seems to be the only way to work with Shapes. Can anybody help? Thanks


	VB:
	
 ClearPictures() 
    With ActiveSheet 
        .Shapes.Range(Array("R1", "R2", "R3", "R4")).Select 
        With Selection.ShapeRange.Fill 
            .Visible = msoTrue 
            .ForeColor.ObjectThemeColor = msoThemeColorBackground1 
            .ForeColor.TintAndShade = 0 
            .ForeColor.Brightness = -0.0500000007 
            .Transparency = 0 
            .Solid 
        End With 
        .Shapes("R1").Select 
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "1" 
        .Shapes("R2").Select 
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "2" 
        .Shapes("R3").Select 
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "3" 
        .Shapes("R4").Select 
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "4" 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I use excel 2007 and have created a heat map on 3 color scale with red fill highlighting the areas of concern, but the text (black color) is not clearly visible in red fills. Is it possible to change the text color to 'white' using an excel function or VBA code? I tried to find the color of cells but have been unable to do so using VBA as the cells are conditionally formatted (due to heat map).
It is not possible for me to change the color coding of cells.

Thanks

Hi,
I am using the following code obtained from this link to change the filter on a pivot table using VBA

http://archive.msdn.microsoft.com/CellFilterPivot


	VB:
	
 
Const RegionRangeName As String = "CustNo" 
Const PivotTableName As String = "CustSales" 
Const PivotFieldName As String = "Customer Number" 
 
Public Sub UpdatePivotFieldFromRange(RangeName As String, FieldName As String, _ 
    PivotTableName As String) 
    Dim rng As Range 
    Set rng = Application.Range(RangeName) 
     
    Dim pt As PivotTable 
    Dim Sheet As Worksheet 
     
    Application.EnableCancelKey = xlDisabled 
     
    For Each Sheet In Application.ActiveWorkbook.Worksheets 
        On Error Resume Next 
        Set pt = Sheet.PivotTables(PivotTableName) 
    Next 
    If pt Is Nothing Then Goto Ex 
     'On Error GoTo Ex
     
    pt.ManualUpdate = True 
    Application.EnableEvents = False 
    Application.ScreenUpdating = False 
     
    Dim Field As PivotField 
    Set Field = pt.PivotFields(FieldName) 
     'Field.ShowAllItems = True
    Field.ClearAllFilters 
     'Field.EnableItemSelection = False
    SelectPivotItem Field, rng.Text 
    pt.RefreshTable 
     
Ex: 
    pt.ManualUpdate = False 
    Application.EnableEvents = True 
    Application.ScreenUpdating = True 
     
End Sub 
Public Sub SelectPivotItem(Field As PivotField, ItemName As String) 
    Dim Item As PivotItem 
    For Each Item In Field.PivotItems 
        Item.Visible = (Item.Caption = ItemName) 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The pivot table field only updates properly based on the contents of the named range "CustNo" the first 2 times that I run it, then it just stops updating. It does not give an error message and the code appears to complete properly but the table is unchanged. If I manually "Show All" items in the pivot table field it starts working again, so I tried adding an initial "Field.ShowAllItems = True" but this did not help.

Does anyone have any ideas why this code is always failing the third time I run it? It does not relate to a specific value. There are about 2000 items in the pivot field, but I didn't think this should make a difference?
Thanks,
janie

Hey whats up?

My application allows users to enter text into a textbox, then that text is pushed to the slide. When the user moves to a new slide, if there is text on the slide it shows up in the textbox (allowing the user to edit it). My problem is that if text on the slide is centered, when it goes in the text box it is now left aligned. My operation to push text from a textbox to the slide is with an onChange function.

The current way I push text from a text box on my form to the slide is by using placeholder:


	VB:
	
 ActivePresentation.slides(ActiveWindow.View.slide.SlideIndex).Shapes.Placeholders 
    For shapeCounter = 1 To .count 
        With .item(shapeCounter) 
            Select Case .PlaceholderFormat.Type 
            Case ppPlaceholderSubtitle 
                .TextFrame.TextRange = Me.txtBodyText.text 
            Case ppPlaceholderBody 
                .TextFrame.TextRange = Me.txtBodyText.text 
            End Select 
        End With 
    Next 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Because I say "the placeholder equals the text box" it changes the alignment on the slide because in the textbox it is aligned left.

Is there any other way to apply text from a textbox and apply it to a slide without changing the format?

I'm sorry if this is confusing.
Thank you for your help.

I use VBA to create a chart with series 1 as individual project data (several projects included), series 2 as a vertical divider line, and series 3 as a horizontal divider line. The HasLegend text defaults to Series 1, Series 2, Series 3. Rather than show each series I would prefer to list the name of each project in Series 1 in the Chart Legend. Any guidance on how to accomplish would be appreciated.

I have to import a text file from an application to Excel so I can analyze
the numbers.

After I have imported the file into Excel, I have to change some cells which
show up as 1069.63- (for instance). To change this to a usable value, the
key strokes I would use are Edit[F2], Home, -, Yes/Enter.

When I use the Macro recorder, I get the following.

Sub CHANGE_SIGN()
'
ActiveCell.FormulaR1C1 = "-1069.63"
Range("I83").Select
End Sub

I want to be able to put the cursor on a selected cell anywhere on the page
and run the macro. As I have recorded the macro, it does not work.

Ideally, the macro could have the ability to change multiple cells, or
ranges on the worksheet.

After I had recorded the macro, on the Stop recording bar, I was not able to
change between absolute or relative referencing, if this is an issue.

Any help is appreciated as there are a number of cells to change.

Hi,

I am using Excel '97. I have a spreadsheet that has information sorted by
date. I would like to have the date cell change to red text if the date is
past the current date (no other info on that line has to change). Is there a
way to do this? Thanks for your help!

Laura

I've been using various resources on this forum to piecemeal together a macro that will help me perform the following task, but it is becoming complex and beyond my reach of comprehension. My VBA knowledge is extremely limited, I can copy and paste and kind of understand things, but being creative and writing things from scratch is beyond my reach.

I need to create a macro that will pull in information from two different sheets in a workbook and paste it into an Outlook mail based on certain conditions and allowing the mail to be in a format that is aligned and readable with a blackberry. I have renamed everything because of confidentiality reasons. I hope it doesn't make it too difficult to understand.

Please see my attached file.

Issues:

1. I need to subject line to change based on the date in the file (cell A2 in Daily Report tab and the Total Daily Produce (cell C10 in Email object tab.)

For example:

Subject: Produce Daily Report 8/28/07 + 7.1lb

The "+" sign in front of "7.1lb" should change to a "-" sign in case it is negative (ha and I know weight can't be negative, bear with my example)

2. Putting together the Email - It needs to pull from two different tabs, Daily Report and Email Object tabs (email object tab is all formulas, so it needs to be pasted as a value). I tried to copy an email macro that would select the sheet and copy it into an email, but I don't know how to pull from the Daily Report tab only cells for produce whose Weight is greater than or equal to 1.0 LB or less than or equal to (1.0) LB. It would need to pull in the "name - description" followed by weight as shown in my example. (This section is where I am having the most problems, it is highlighted in yellow on my desired email tab.) The rows in the Daily Report tab change constantly, I would need to be able to insert new rows and add types of fruits under the bolded headings (Apples, Bananas, Peaches, etc... will stay the same, it's the breakdown below that will fluctuate)

3. Another issue is how can the email object columns be preserved so that they are aligned right within the column and visible and accessible with the blackberry as well as on a regular computer. I noticed within the forum that someone said it can be done, but it does not go into detail. Can I do this using the Times New Roman font?

4. Finally, I need to have the file attach to the email. I remember reading somewhere how it was done, but I'm not sure how this would affect formatting in other areas of the email.

Any suggestions on what I can do to make this work would be greatly appreciated! I create this email every day and it's tedious and mind numbing, and I know that excel can do it for me!

Hi guys,I'm trying to insert a text box using vba, but the text box needs to be the same size as the cells that a user has selected. It's essentially for a time-line so that a user can highlight the cells (months) they want and click a button to insert the textbox to cover all those months. The selection would only ever be on the same row. I'm using Excel 2002 (long story why). I can figure out how to insert and size the text box based on an active cells size, but not multiple cells. What i have so far is shown below:

	VB:
	
 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If anyone can help, i'd be forever grateful!