Skip to main content

Posts

Showing posts with the label Word

Set a graph or shape in Word behind text using VBA

How to set a graph or shape in Word so it stays behind the text using VBA?
In Word the option is to right click an object select wrap text and click the desired options such as behind text, in front of text and other options.
Setting a graph behind the text is quite useful, if you need to display some text that will be displayed together with the graph.
To do this via VBA is plain and simple, a one liner code.
ThisDocument.Shapes("NameOfTheChart").WrapFormat.Type = wdWrapBehind
wdWrapBehind is equal to set "Behind Text".
The one liner VBA code, of course it will apply to any shapes, circle, oval, square or other shapes not just a graph.
Code works on Word 2016, it may or may not word in previous version of  Word. 

Cheers. Till next time!


================================ Free Android Apps:
Click on links below to find out more:
Catholic Rosary Guide  for Android:
https://play.google.com/store/apps/details?id=com.myrosaryapp
http://quickbytesstuff.blogspot.sg/2014/09/how-t…

How to position a graph in word in a specific page?

Positioning a graph in word is necessary to put the graph in a page where the description or other supporting details for the graph will be placed.
But, how to place or put a graph in a specific page in a document?
By default, the graph will be generated in the first page of the document.
To position a graph in any page in the document is to create a bookmark.
If you know exactly where to put the graph, manually create a bookmark by clicking  “Insert” in the ribbon and selecting “Bookmark”.
After clicking “Bookmark” it will ask for a name and create any name to define the bookmark.
Once the bookmark has been named and created, you can call the bookmark via VBA.
Here’s the code to call and select a bookmark:
ActiveDocument.Bookmarks("graph1").Select
graph1 – is the name of the bookmark.
After selecting the bookmark via VBA, call the method to generate the chart.
Like:
'initialize and set graph type
 Set shp = ActiveDocument.Shapes.AddChart(xlXYScatterLines, -18, 80, 480, 3…

Word VBA to set x and y chart values

How to set X and Y values in Word VBA chart?

How to set vertical and horizontal values for a Word chart using VBA?

Code below assumes that you already have the necessary code to generate the chart.

This code below will only show how to set the X and Y values for a chart using VBA in a Word document.

Here's the code:

'Set value for Y axis or Vertical value

With cht.Axes(xlValue)

   .MinimumScale = 0
   .MaximumScale = 35
   .MajorUnit = 5
   .MinorUnit = 0

End With


' Set value for X axis or Horizontal value

With cht.Axes(xlCategory)

   .MinimumScale = 0
   .MaximumScale = 25
    .MajorUnit = 5
   .MinorUnit = 0    

End With




Cheers! Till next time.



================================ Free Android Apps:
Click on links below to find out more:
Excel Keyboard shortcuts guide https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide
Linux Android App cheat sheet: https://play.google.com/store/apps/details?id=com.LinuxMobileKit Multiplication Table for early learnershttps://play.goo…

VBA Word change graph data point width

If you have been playing with the chart in Word or Excel you might encountered that the point on the graph is quite small.
If you need to adjust the data point on Word graph, you have two options do it manually or simply automate it at runtime using a VBA code.
Assuming you have all the necessary parameters that creates the graph via VBA, and you just need to adjust the data point you can use the code below, the code below was tested using Word 2016. It may not apply to older versions but I think logic should stay the same.
cht.SeriesCollection(1).Format.Line.Weight = 9.75
To do it manually, click on the data point and right click on it and select “Format Data Point”, click the bucket icon and change the width value.

Here’s the screen shot using Word 2016:

For the sake of completeness, the code will be something like this:
Dim shp As Shape Dim cht As Chart  
Set shp = ActiveDocument.Shapes.AddChart(xlXYScatterLines)   Set cht = shp.Chart
Dim wb As Excel.Workbook Dim ws As Excel.Worksh…

How to rename a chart in Word 2016

Do you want to check what’s the name of the chart in Word 2016?
Or you just want to rename the chart in Word 2016 to a name, that the chart represents?
Word 2016 default chart name is in sequence, like Chart1, Chart2, Chart3 and it follows this pattern depending on how may charts you have at your document.

If you have multiple charts in a word document, and you want the chart name it to something meaningful or what the chart itself is all about; it’s quite easy and straight forward to rename a chart, but the way to rename it is not conventional as the previous way of doing it in other version of office.
You can rename the chart via VBA code or using the GUI in Word itself.
To rename or check the name of the chart in Word 2016 or O365.
Click on the chart:

After clicking the chart, the "Chart Tools" will open at the Ribbon.
Click the "Chart Tools"  at the ribbon. Click on the "Format" tab.
Under Format tab, click on "Selection Pane", this will op…

Excel VBA set worksheet visibility

Set excel worksheet visibility by changing its property.
Simple and a one liner code to hide and unhide worksheet in Excel.
Sub Visible_Sheet()
Worksheets(1).Visible = 1  'Make the worksheet visible
End Sub
Sub Hide_RightClick_Unhide()
Worksheets(1).Visible = 0    'Hide Worksheet to Unhide Right click on Sheet name and unhide the worksheet
End Sub
Sub Super_Hide()
Worksheets("calcx").Visible = 2
 'Hide Worksheet but right click on Sheet name will not show whether the sheet is hidden or not
End Sub



Cheers! Till next time.

================================ Free Android Apps:
Click on links below to find out more:

Excel Keyboard shortcuts guide https://play.google.com/store/apps/details?id=chrisjoms.myexcelapplicationguide
Linux Android App cheat sheet: https://play.google.com/store/apps/details?id=com.LinuxMobileKit
Multiplication Table for early learners https://play.google.com/store/apps/details?id=com.TableMultiplication
Catholic Rosary Guide  for Android:
https://play.google.com/sto…

Excel find last row with value in a specific column

1. How to find last row with value using vba in a specific column?

2. How to find last row with value  using vba in a worksheet?

The questions above are entirely two different scenarios.

The first question will find the last row with value in a specific column within the worksheet.

While the second question find the last row with value of the entire worksheet.

The first question is applicable if you need to find the last row in different columns.

Let's say Column 1 or Column A, or Column 2 or Column B etc.

Example code below will find the last row in a specific column via VBA code.

===================
'command Cells(Rows.Count, 1) = find the last row with value in Column 1 for Sheet3

Dim last_row As Integer

last_row = Worksheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row

Msgbox last_row

'replace 1 with the desired column 
'ex: command Cells(Rows.Count, 7) = find the last row with value in Column 7
===================

If you don't need to specify a worksheet or…

How to insert landscape orientation in Word?

If you are writing a thesis, a report or an article that you need to insert a landscape layout in a specific page in Word document but your orientation all long is portrait and changing the orientation causes the whole layout to change.
Inserting landscape in a specific page in a word document can be done by inserting section breaks. Let’s just make a basic example.
Let’s say you have 3 page document and you want that first page in Portrait mode, second page in landscape and third page in portrait layout also.
First, click on “home” tab, click the “show/hide paragraph marks” option or see the icon below. Enabling this option the section breaks, spaces and tabs will be visible. But what we are interested is to see the “section breaks”.


In the first page position the cursor on the last area at the bottom of the page.
In Word 2010, click on “page layout” in the ribbon tab. Click on “breaks” and select “next page”. In the second page, position also the cursor at the middle or bottom of t…

Outlook does not display picture

Have you received an email saying please find the picture below, but the picture is on the attachment not on the body of email.

Or sending out a nice an beautiful layout of graphics hoping to impress a colleague or client only to find out that the the graphics doesn't display correctly in Outlook or either in the mobile phone.

HTML Tables has been a big help for web designers or developers before the CSS era or other good platforms at this time.

Well, if picture is not displaying properly in Outlook or mobile phone. Embedding or inserting the picture in a Table, seems to be a good quick solution in inserting pictures via Outlook and it will display properly in mobile phone also.

When composing email in Outlook, click on the Insert option and click on insert table. If sending two pictures, you can insert a two rows or two columns and insert the picture on each row or column.

After sending out the email, check the email in Outlook or mobile phone and if everything goes okay the pic…

Word VBA Select Page

How to select a page in Word VBA?

Sub Select_Home()

'Select the first page of the document (page 1)
'Like pressing Ctrl + Home

Selection.GoTo wdGoToBookmark, , , "\StartOfDoc"


End Sub

Sub Select_document_end()

'Select the end of the document
'Like pressing "Ctrl+End"

Selection.GoTo wdGoToBookmark, , , "\EndOfDoc"

End Sub

Sub Select_via_Page_Number()

'specify the page number
'Example below will select page 5

Selection.GoTo wdGoToBookmark, , , "5"

End Sub

Tested and working using Word 2010.

Cheers..till next time :) Happy coding.

================================
Free Android Apps:

Click on links below to find out more:

Linux Android App cheat sheet:
https://play.google.com/store/apps/details?id=com.LinuxMobileKit


https://play.google.com/store/apps/details?id=soulrefresh.beautiful.prayer

Catholic Rosary Guide  for Android:

https://play.google.com/store/apps/details?id=com.myrosaryapp

http://quickbytesstuff.blogspot.sg/2014/09/how-to-r…

Word calculate values in table

Microsoft word uses basic formula calculation such as getting sum values, counting the number of rows in a table, product calculation, taking average values and some other functions which can be easily use from the drop down function of the formula.
Word is not a spreadsheet so calculation formulas are limited; of course Excel is the suited tool if the task requires more extensive calculation.
Below is a screen shot on how to use Microsoft Word formula to access basic function for calculation.
From the ribbon tab, insert a Table then click on “Table Tools”, click on “Layout” and select formula from the options.
If there is no Table inserted in Word. “Table Tools” will not be visible and also the formula functions.

See screen shot below:



Cheers.. till next time..

Excel formula does not recalculate automatically

Formulas in a worksheet do not automatically recalculate.
By default formula recalculation in Excel is set to automatic but if it has been changed to manual you can set it back to auto.
To set the calculation to auto or manual, click on the “Formula” tab then in the right had corner click on “Calculation options” and select the desired option.

See screen shot below:


GIMP start a new path or selection

Start a new path when creating lines in GIMP.
How to create lines in GIMP?
Select the pencil tool from the toolbox, make sure the foreground color is not the same with the background color or else the line will be invisible.
If you need to specify a brush select the "brush" that you want to use to create the line.
Press and hold the "shift key" then select or click the start point.
Click the second point (while still holding the shift key) and a line will be drawn. Release the "shift key".
If you want to draw again a new line, press "shift key" but you will notice that the line will be connected to the last point that the line was drawn.
This will happen if you didn't "double click" on the last point that the line was drawn.
Even if you press "esc" key it will still continue from the old point or the new line is still connected to the end of the line that was previously drawn. Or if you’re doing a selection the new area…

Excel VBA replace first or last character

Excel VBA code below will replace the first or last character of the cell value.

Use a test workbook with dummy data, to test the VBA code.

Works fine on Excel 2010.

Sub replaceChar()
'Replace Last Character

Dim i, ilength As Integer
Dim strCellValue, yCutString, zValue As String
Dim iRow, myColumn As Long

'specify the column where the values will be changed
myColumn = 2

'get the last row
iRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row

For i = 1 To iRow
On Error Resume Next

strCellValue = Cells(i, myColumn).Value
ilength = Len(strCellValue) - 1

'left - will replace last character on the string
yCutString = Left(strCellValue, ilength)

' LastChar is the string that will be added to the end of the cell value
'Replace this with any desired value
zValue = yCutString + "LastChar"

'Display the value of the processed string
'press ctrl+break to stop the loop
MsgBox zValue


'uncomment this line to replace the value with the processed string
'specify…