Free Microsoft Excel 2013 Quick Reference

MICROSOFT barcode control -changing the barcode image in realtime


I don't know if anyone of you has ever used "MICROSOFT barcode control 14.0" .
I certainly haven't. But now I need this thing to work.

If you could look at the attached file, there are three distinct colors: light blue; green and pink.
What you do is insert a particular PRODUCT NUMBER in row"B" , which gets you a BARCODE Number at row"D".
That is why in the Cell"D2" the number is 0001044034204, and it is exactly as the blue barcode's image.

The problem is that the BARCODE IMAGE doesn't change automatically after using different product number in the row"B".
It changes if you "MOVE" or "CHANGE THE SIZE" of the barcode image.

Does anyone know how to make the image change automatically?
Or any other way to show a barcode image which does what I want.

Any help is appreciated.
Thank you

K. I will try and explain this the best I can.

I am creating a database based on ships.
I have around 50 thumbsized images and I have resized a cell to fit these
images in the main page that gives information about each ship.
I need this cell to be able to change the image each time the name of the
ship changes.
I can't store these images on my HD and link to them as other people useing
the database would have to have the same images as well.
I would like to paste all the images into a seperate sheet in the workbook.
The only way I can think of doing this would be to lock an image to a cell
and then use something like VLOOKUP but I can't find a function that allows
you to lock an image to a cell.

Any help on this would be apreciated.

Thank you.

Hey folks,

Yet again your pesky irish man here with yet another question hahaha

I was just wondering how would I create a dropdown menu that contains the years 2006 up to 2009 and when a year is selected it change the data in a bar chart to the info from a corresponding worksheet.

I have a chart for a number or items referred to my dept each month and the boss would like a chart to show the number of referrals each year on a monthly basis. As stated I have a sheet for each year and would like to be able to create a drop down menu above one chart so that it will show/change the chart for each year as it is selected from the drop down menu :P

Any ideas?

Thanks a million in advance,

I have used a list in a validation drop box, but the text size when veiwing
the list is very small. How do I increase the text size in the drop box? Once
a item is selected off the list it is shown in the validation cell as per the
cell format. I have tried to change the format in the list, but this doesn't
change the viewed size of the validation cell drop box.

I'm trying to change the font in a check box created from the 'Forms
Toolbar', is it possible to change it and if it is how can i go about doing

Thank you in advance!!


Does anyone know if it is possible to assign a value to a number, say 1, that
when entered into a cell it will automatically change the formula in
corresponding cells. EG. If 1 = +10days
I enter 1 in b3, then the formual in b5 will change from +20days, to +10days

I don't even know if this is possible with excel, but would very much
appreciate any feedback possible. Thanks.

When I change the style in column (a) a date, it also changes column (b)
which I want to be the time


Sorry for the earlier post, but hope this is following the roules!

Where do I change the range in this VBA code? The range hase to be E14:G214 as below but it's not working.

Private Sub
Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Intersect(Target, Range("E14:G214")) Is Nothing Then
    With Target
    Select Case .Column
        Case 1
            If IsDate(.Cells) And IsNumeric(.Offset(0, 2)) And Not IsEmpty(.Offset(0, 2)) Then
                .Offset(0, 1).Value = .Value + .Offset(0, 2).Value
            End If
        Case 2
            If IsDate(.Cells) And IsDate(.Offset(0, -1)) Then
                .Offset(0, 1).Value = .Value - .Offset(0, -1).Value + 1
                .Offset(0, 1).NumberFormat = "General"
            End If
         Case 3
            If IsNumeric(.Cells) And IsDate(.Offset(0, -2)) And Not IsEmpty(.Cells) Then
                .Offset(0, -1).Value = .Offset(0, -2).Value + .Value - 1
            End If
    End Select
    End With
End If
Application.EnableEvents = True
End Sub

While opening excel, all the excel addins gets loaded in alphabetical order.
Is there any option for changing the order in which they are to be loaded,
i.e. as per our wish.
Can anybody help me out.


How do I use autofill to change the dates in a COUNTIF formula? Example:
A1 cell: =COUNTIF(B1:B100, "1/1/2010")
A2 cell: =COUNTIF(B1:B100, "1/2/2010")

I want it to autofill the formula with 1/3/2010, 1/4/2010, etc. when I drag it down, but it just repeats the first 2 dates over and over. Any suggestions? Thanks!

Hi all,

In one workbook I have a number of worksheets, all of which displays in cell B5 the same value. These all use "=" to auto-update when the cell in the first sheet is changed.

My question is as follows: is there a way to change the value on each worksheet by changing the cell in any worksheet? Currently each value will change only if I change the value in the first cell. If I try to type something in any other cell, the formula will be overwritten.


I have a SS that haas multiple columns on it (atttached)

Column H contains a formula that is a vlookup. If the info returned is less than 0 I need a formula that changes the number in cell A1 to zero.

any help would be great...I m a novice excel guy.


Using VBA 6.3 with Excel 2003, I'm trying to change the range in a chart with 2 series:

Set WS = Worksheets("Graph_Sheet")
    ActiveChart.SeriesCollection(1).XValues = "='DataSourceSheet'!R8C1:R18C1"
    ActiveChart.SeriesCollection(1).Values = "='DataSourceSheet'!R8C54:R18C54"
    ActiveChart.SeriesCollection(2).XValues = "='DataSourceSheet'!R8C1:R18C1" '<---Error Line
    ActiveChart.SeriesCollection(2).Values = "='DataSourceSheet'!R8C53:R18C53"

But, when executing, I get this error message in the "Error Line":

What am I doing wrong? Thank you very much in advance


how can i change the range in the row below into a variable


HI All I was wondering if there was a way to create a parent pivot table and 2 children based on the first pivot table, and when you change the criteria in the parent table the children pivot table changes accordingly to those criteria changes?
Can this be done? Will this need to be done in VB?

This so simple and seems so easy, but I don't know how to do change the case in Excel 2007. I don't want to this automatically done, because I am already done with the document except of changing the case of some words in a section of the spreadsheet.

I want to select a cell series and capitalize the words or Title case all the words.

So it change from "Marshmellow smore" to "Marshmellow Smore."

or I want to be able to lowercase a select cell series. Example:
"Marshmellow Smore" to "marshmellow smore."

Thx for your help

If you've tried to change the printer in Excel VBA, no doubt you've realised that it needs the printer port in order to work, this looks something like "NE01:". As this changes from computer to computer it can't be hard coded.

Here's a solution that will get the printer port without looping over all possible versions until one works.

Public Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
"RegOpenKeyExA" (ByVal hKey As Long, ByVal lpSubKey As String, _
ByVal ulOptions As Long, ByVal samDesired As Long, phkResult As Long) As Long

Public Declare Function RegQueryValueEx Lib "advapi32.dll" Alias _
"RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As _
String, ByVal lpReserved As Long, lpType As Long, lpData As Any, _
dwSize As Long) As Long

Public Declare Function RegCloseKey Lib "advapi32.dll" _
(ByVal hKey As Long) As Long

Private Const dhcKeyAllAccess = &H2003F
Private Const HKEY_CURRENT_USER = &H80000001
Private Const dhcRegSz As String = 1

Function GetPrinterPort(PrinterName As String) As String
    Dim hKeyPrinter As Long
    Dim lngResult As Long
    Dim strBuffer As String
    Dim cb As Long
    lngResult = RegOpenKeyEx(HKEY_CURRENT_USER, "SoftwareMicrosoftWindows NTCurrentVersionDevices", 0&,
dhcKeyAllAccess, hKeyPrinter)
    If lngResult = 0 Then
        strBuffer = Space(255)
        cb = Len(strBuffer)
        lngResult = RegQueryValueEx(hKeyPrinter, PrinterName, 0&, dhcRegSz, ByVal strBuffer, cb)

        If lngResult = 0 Then GetPrinterPort = Right(Left(strBuffer, cb), 6)
        lngResult = RegCloseKey(hKeyPrinter)
    End If
End Function
Below is an example for calling it:

Hope this helps someone

Hopefully this will be quick.

I have dates in column N and notes in column O.
I want to change the color in column O if the date in column N is equal to or older than today.
For instance if N2 is 9/1/06 then O2 should be red. Same with N3 and O3, etc.

I've tried "Format only cells that contain" "cell value" "less than or equal to" =N2:N300=TODAY() and applied it to $O$O and everything in O turns red. I've also applied ti to $O#2:$O$300 and the same thing happens.

Anyone have ideas?

I need a macro to change the dates in header and footer for all word documents within certain directory. Any ideas?

Dear Friends,
I am not as good in VBA, usually only clean up and edit the recorded code.
I am really struggling to figure out how to load the different images into cell.
My case has 5 different colours of buttons which I would like to appear in the cell
upon changing the value in it egg. 1,2,3,4,5 or r (red), y (yellow), g (green), b (blue), w (white).
Can someone please help me to do it in a nice manner?
I appreciate any hints,
Thank you,


I tested the following VBA-code (see below) in excel 2000 on Windows XP. And it works.

The code(see below) is used in a userform called CalendarUserForm (height 216 points, width 170
points) with
two CommandButtons (OKButton, CancelButton) on the userform. On the userform, the code creates
a month-calendar-control using the WIN-API.

I know i could use the Microsoft MonthView Control ActiveX-object in MSCOMCT2.OCX.
But it is difficult to distribute and install these ocx-files on other systems.

info about the month-calendar-control:

I have a few questions about the code(see below):

1) Is this the correct way to create and use the month-calendar-control in VBA?
Is it correct code in general? Any remarks, comments about it?

I know i could use the Microsoft MonthView Control ActiveX-object in MSCOMCT2.OCX.
But it is difficult to distribute and install this ocx-files on other systems.

2) The month-calendar-object sends notification messages (MCN_SELCHANGE,
MCN_SELECT, MCN_GETDAYSTATE) to the parent(the userform) when it receives
user input. Currently, I ignore these messages. I do not process these messages.
Should i process these messages or is it safe not to process them?

3) What about destroying the month-calendar-object in UserForm_Terminate()?
Is it correct to send a WM_CLOSE-message to the object before using the DestroyWindow-call?
Or should I only perform the DestroyWindow-call ?

4) I use InitCommonControlsEx(). (A WIN-API-function).It registers the common control classes.
Is it safe the call this function several times, because it is called everytime ShowResult is
Is there an opposite function which unregisters the common classes?
Something like UnInitCommonControlsEx



the code from CalendarUserForm.frm

Option Explicit

' private variables

Private cal_result As VbMsgBoxResult ' return-value of ShowResult
Private cal_selected_date As Date ' current selected-date
Private cal_control_handle As Long 'handle to month-calendar-control

' const month-calendar-control
Private Const MCM_GETCURSEL As Long = &H1001
Private Const MCM_SETCURSEL As Long = &H1002
Private Const MCM_SETTODAY As Long = &H100C

Private Const ICC_DATE_CLASSES As Long = &H100
Private Const MONTHCAL_CLASS As String = "SysMonthCal32"

' windows-message
Private Const WM_CLOSE As Long = &H10

' const style
Private Const WS_CHILD As Long = &H40000000
Private Const WS_VISIBLE As Long = &H10000000
Private Const WS_BORDER As Long = &H800000

dwSize As Long
dwICC As Long
End Type

wYear As Integer
wMonth As Integer
wDayOfWeek As Integer
wDay As Integer
wHour As Integer
wMinute As Integer
wSecond As Integer
wMilliseconds As Integer
End Type

Private Declare Function MonthCal_GetCurSel Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As SYSTEMTIME) As Long

Private Declare Function MonthCal_SetCurSel Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByRef lParam As SYSTEMTIME) As Long

Private Declare Function InitCommonControlsEx Lib "comctl32" _

Private Declare Function CreateWindowEx Lib "user32" _
Alias "CreateWindowExA" _
(ByVal dwExStyle As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String, _
ByVal dwStyle As Long, _
ByVal x As Long, ByVal y As Long, _
ByVal nWidth As Long, _
ByVal nHeight As Long, _
ByVal hwndParent As Long, _
ByVal hMenu As Long, _
ByVal hInstance As Long, _
lpParam As Any) As Long

Private Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hwndParent As Long, _
ByVal hwndChildAfter As Long, _
ByVal lpszClass As String, _
ByVal lpszWindow As String) As Long

Private Declare Function DestroyWindow Lib "user32" (ByVal hWnd As Long) As Long

Private Declare Function SendMessage Lib "user32" _
Alias "SendMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Declare Function PostMessage Lib "user32" _
Alias "PostMessageA" _
(ByVal hWnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

' -------------------------------------------------------------------------------------

' Shows userform after it created the month-calendar-control
' the function returns when the user clicks on the OK-button or Cancel-button
' (SelectedDate is the date selected in the month calendar object)

Public Function ShowResult(ByRef SelectedDate As Date) As VbMsgBoxResult
On Error GoTo errorhandler

' strip time-part of the selected date
SelectedDate = DateSerial(Year(SelectedDate), Month(SelectedDate), Day(SelectedDate))

OKButton.Enabled = False

cal_result = vbCancel ' returned when you use the upperright Close-button
cal_selected_date = SelectedDate
cal_control_handle = 0&

' determine handle to userform
Dim prevCaption As String
Me.Caption = CStr(Timer())

Dim userform_handle As Long
If Val(Application.Version) < 9 Then
userform_handle = FindWindow("ThunderXFrame", Me.Caption) 'XL97
userform_handle = FindWindow("ThunderDFrame", Me.Caption) 'XL2000
End If

Me.Caption = prevCaption

If userform_handle 0& Then

' determine handle to parent of month calendar control
' the parent is the first child of the userform

Dim parent_handle As Long
parent_handle = FindWindowEx(userform_handle, 0&, vbNullString, vbNullString)

If parent_handle 0& Then

initCtrlsStruct.dwICC = ICC_DATE_CLASSES
initCtrlsStruct.dwSize = Len(initCtrlsStruct)

If InitCommonControlsEx(initCtrlsStruct) 0 Then

' create month calendar control

cal_control_handle = CreateWindowEx(0, MONTHCAL_CLASS, vbNullString, _
WS_BORDER Or WS_CHILD Or WS_VISIBLE, 10, 10, 200, 200, _
parent_handle, 0&, 0&, ByVal 0&)

If cal_control_handle 0 Then
OKButton.Enabled = True
End If
End If
End If
End If

' show the userform with the month-calendar-object
' wait untill form is hidden

ShowResult = cal_result

If ShowResult = vbOK Then
SelectedDate = cal_selected_date
End If

Unload Me

Exit Function

ShowResult = vbCancel
Unload Me
End Function

' set cal_result and hide userform

Private Sub CancelButton_Click()
cal_result = vbCancel
End Sub

' determine current selected date
' if it succeeds to determine, set cal_result to vbOk and hide the userform

Private Sub OKButton_Click()
On Error GoTo errorhandler

If cal_control_handle 0& Then
Dim selected_system_time As SYSTEMTIME

If MonthCal_GetCurSel(cal_control_handle, MCM_GETCURSEL, 0&, _
selected_system_time) 0 Then

' strip time-part of current selected date

With selected_system_time
.wHour = 0
.wMinute = 0
.wSecond = 0
End With

cal_selected_date = SystemTimeToDate(selected_system_time)

If CDbl(cal_selected_date) >= 0 Then
cal_result = vbOK
End If
End If
End If

Exit Sub

cal_result = vbCancel
End Sub

' close month calendar control
' destroy month calendar control

Private Sub UserForm_Terminate()
On Error Resume Next

If cal_control_handle 0& Then
SendMessage cal_control_handle, WM_CLOSE, 0&, ByVal 0&
DestroyWindow cal_control_handle

cal_control_handle = 0&
End If
End Sub

' event is fired when you use the upperright Close-button

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Error Resume Next

' prevent unload !
' just hide it

If CloseMode = vbFormControlMenu Then
cal_result = vbCancel
Cancel = 1
End If
End Sub

Private Function SystemTimeToDate(ByRef system_time As SYSTEMTIME) As Date
With system_time
SystemTimeToDate = DateSerial(.wYear, .wMonth, .wDay) + _
TimeSerial(.wHour, .wMinute, .wSecond)
End With
End Function

Private Function DateToSystemTime(ByVal Date_Value As Date) As SYSTEMTIME
Dim system_time As SYSTEMTIME

With system_time
.wYear = Year(Date_Value)
.wMonth = Month(Date_Value)
.wDayOfWeek = Weekday(Date_Value, vbSunday) - 1
.wDay = Day(Date_Value)
.wHour = Hour(Date_Value)
.wMinute = Minute(Date_Value)
.wSecond = Second(Date_Value)
.wMilliseconds = 0
End With

DateToSystemTime = system_time
End Function

An example of how the userform CalendarUserForm is called:
(SelectedDate is the date selected in the month calendar object)

Public Function ShowCalendar(ByRef SelectedDate As Date) As VbMsgBoxResult
On Error GoTo errorhandler

ShowCalendar = CalendarUserForm.ShowResult(SelectedDate)

Exit Function


ShowCalendar = vbCancel
End Function

When placing an image in an image control in a user form it looses quality when compared to the same image in an image control in a worksheet (same sizes).
I tried to improve the quality of the image, but it didnīt work, the image is still not clear when I place it in the user form...
Any ideas of how to solve this problem?
Thank you Auto Merged Post;

Here is an example of my problem.

In Sheet1 there is an image of a window.
Pressing the ComandButton1, the same image appears in a userform but with too bad quality...

The image in both situations is the same...

Any idea of how to solve this problem?

Thank you

if I want to change the picture in an image of a userform i understand that i can use the following code:

image1.picture = loadpicture(filepath)

But I want to change the picture to the same image as one of the shapes in workbook. Anyone know how to do this????


I got a rather strange problem, I am sure this is somehow a 'feature' but an unwanted one from my perspective.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI6J6=
ABCDEFGHIJK4           5 JanFebMarAprMayJun Q1Q2 6 100010001000    30000 7           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I am trying to enter a number for each of the months. I6 and J6 contain sum of subsequently the first and second 3 months. Once I get to april and fill in let's say another 1000. Excel Automatically changes the formula in I6 to include April.

******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutI6J6=
ABCDEFGHIJK4           5 JanFebMarAprMayJun Q1Q2 6 1000100010001000   40001000 7           Sheet1 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

I know the workaround is to make the references absolute, but I was wondering why this would happen ?

How is is possible that Excel is allowed to change the formula ?

To me this is very dangerous because it undermines the integrity of the workbooks.
Is there any way to disable this kind of behaviour ?



I am using image control in xl sheet, and showing one gif file. I want to change the image at run time. Please let me solution for that.