Skip to main content

Posts

Showing posts with the label VBA

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.

Kindly help to fill out this survey:(social media effects)
https://1drv.ms/xs/s!AoHcvCpSMSIdjEuJzjCHl4sMh2su
================================ 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…

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…

PowerShell get folder capacity

Get the folder capacity using PowerShell via the old-school method.
Old-school since we will utilize the native “dir” command, just like the good old days of DOS.
PowerShell dir command output is not the same with the native windows system32 dir command line just like in DOS system.
The output will be in bytes since it is from the command line but of course, if you are command line junkie it will be easy to read even if the capacity is written in bytes.
First, fire up notepad write the dir command plus the full folder path of the specified folder in which you like to monitor or want to know the capacity.
 dir “c:\users\dmusic\music folder 001”
The path is enclosed in quotes since the folder has spaces and save the notepad as a batch file with “.bat” extension.
Open PowerShell command line window or PowerShell ISE and type the following command:
#change the path to where the batch file was saved
$folder_base_cap = d:\read_folder_cap.bat 
$ folder_base_cap | Select-String "bytes&qu…

Sort numbers, digits or figures in Excel 2016

To sort numbers, digits, or figures in Excel 2016 is quite straight forward.
There are two ways to do it via the Home tab or Data tab.
What is found on the Home tab is sorting of alphabets or A-Z option which can easily be seen beside the auto-sum function which is on the right most tab of the ribbon by default.
Screen shot below shows the sorting functionality found in Home tab.


The other way to sort by numbers, digits or figures in Excel 2016 is to click on “Data” click on “Sort” then an option will appear to sort by smallest to largest or largest to smallest.
A screen shot below shows where to find the function.


That’s it hopes it helps to make thing easier.


================================ 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 learner…

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…

PowerShell validate list of email addresses

Validate list of email addresses in a text file and check which email is a valid email and which one has the incorrect format.
PowerShell code below can easily check or validate incorrect email, output will show true if valid email and false if the email is not valid.
To check list of valid emails PowerShell utilizes regex. This is useful to check typo errors when sending out mass email or a list of emails which has not yet been verified.

But of course, the code will not be able to check whether the emails are active or not.

#===============================
$reader = [System.IO.File]::OpenText("c:\all_emails.txt") #get-content can also be used
while($null -ne ($line = $reader.ReadLine())) {
    #$line     $regx="[a-z0-9!#\$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#\$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
    [regex]::Match($line, $regx, "IgnoreCase ")  |  format-Table success, value -AutoSize }

#========…

How to escape in PowerShell

In bash script the backslash is use as escape sequence and even in Java programming.

But what's the equivalent of bash escape sequence in PowerShell?

Grave accent or back tick, which is found at the top left corner of the keybord below the "esc" key on some keyboard, This character "`" is the escape in PowerShell.

For example:

Write-host "This line is above `r`n and  `r`n this line is below"

Output is:
This line is above
 and
 this line is below

r is equal to carriage return character putting a backtick before r, `r tells PowerShell that r is not a character but a carriage return
n is a new line character putting a backtick before n, `n tells PowerShell that n is a new line character.

Another example:

$ sign in PowerShell is to indicate a variable

But what if you need to display a dollar sign as a character and not as a special character to declare a variable. Then we need to escape the dollar sign by prefixing a backtick before the character.

Exampl…

Excel VBA load all text files to listbox

Get all text files in a specified path to a  userform listbox using VBA.

The code below will read the directory for all text files and its filename will be loaded to listbox.

=====================

Dim fs, f, fc, f1

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder("C:\text_folder")

    Set fc = f.Files

    For Each f1 In fc

        If InStr(1, f1.Name, ".txt") Then

           'add all filenames to listbox
           UserForm1.ListBox1.AddItem f1.Name

        End If

    Next

=====================

Happy coding!


Cheers..till next time.



================================ 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
Multiplication Table for early learners https://play.google.com/store/apps/details?id=com.TableMultiplication
Catholic Rosary Guide  for Android:
https://play.google.com/store/apps/details?id=com.myrosaryapp
http://quickbytesstuf…

Excel VBA copy or reference a value

Excel VBA provide options on how to pass value from worksheet to worksheet or from workbook to another workbook.
To optimize or make the VBA code run faster is to make the worksheet interaction lesser.
Recording an Excel VBA macro to copy and paste a value will have a similar output to the code below.
Sub Macro1() ' ' Macro1 Macro '     Range("A2").Select     Selection.Copy
    Sheets("Sheet2").Select     Range("A3").Select     ActiveSheet.Paste
End Sub
what the code does is to select the cell on the active sheet for copying and select another worksheet and select the cell where the value will be placed and paste the copied cell value.
Steps are: a. Select the cell and copy the value b. Select the worksheet where the value is to be copied c. Next step, is select the cell on the selected worksheet d. Paste the value on the cell
Steps are absolutely fine, if it works as expected. But if you are copying hundreds or thousands of cells. It will be a w…

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…