Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Unwanted Code

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Each time I open Excel and press [Alt] [F11] in a blank workbook, I have the following code:

'Entry point for RibbonX button click
Sub ShowATPDialog(control As IRibbonControl)
    Application.Run ("fDialog")
End Sub

'Callback for RibbonX button label
Sub GetATPLabel(control As IRibbonControl, ByRef label)
    label = ThisWorkbook.Sheets("RES").Range("A10").Value
End Sub

How can I permanently delete this?
Thank you

View Answers     

Similar Excel Tutorials

Quickly Copy Cell Formatting to Other Cells in Excel
How to copy all of the formatting of a single cell to other cells in the spreadsheet without having to re-apply eac ...
Get the Name of a Worksheet in Macros VBA in Excel
How to get the name of a worksheet in Excel using VBA and Macros and also how to store that name in a variable for ...
Run a Macro from Another Macro in Excel
I will show you how to run a macro from another macro in Excel.  This means that you can run any macro when you ne ...
Loop through All Worksheets in Excel using VBA and Macros
Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros. This only take ...

Helpful Excel Macros

Excel Macro to Save a Specific Worksheet as a New File
- This Excel Macro allows you to save a specific worksheet within the Excel Workbook to its own new file. You will be a
Macro to add a New Line to Message Box Pop-up Windows in Excel
- This is a very simple Message Box, pop-up window, macro for Excel that illustrates how to put new lines, the same thi
Save the Current Worksheet as a New Excel Workbook File
- This Excel Macro will save the currently visible/active worksheet (the one that you see when you run the macro) to a
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth

Similar Topics


I opened excel this morning to discover 2 files in the VBA editor that I've not seen before. One is called FUNCRES.XLAM with the following code in a module


'Entry point for RibbonX button click
Sub ShowATPDialog(control As IRibbonControl)
    Application.Run ("fDialog")
End Sub
'Callback for RibbonX button label
Sub GetATPLabel(control As IRibbonControl, ByRef label)
    label = ThisWorkbook.Sheets("RES").Range("A10").Value
End Sub

The other is ATPVBAEN.XLAM which when I try to open is password protected.

Firstly what is the XLAM part of the file? secondly I didnt create them and no one else has any idea what they are so how could they get there? and thirdly is there any way of deleting them? I cant remove them for some reason. I know this is pretty random but just seeing if anyone else had experienced this type of thing.

Thanks in advance

Hi everyone,

I want to programmatically call procedure, which is an callback for ribbonX control. I know, that there is control parameter dimensioned as IRibbonControl. I was thinnking, that I can simply create variable of this type and assigned it to the button on the ribbonX, but probably this could not be done

Do you have any solution to the given problem?

I have an xlam AddIn which creates a custom tab in Excel. I want to allow users to choose their own keytip for this tab using the getKeytip functionality of Ribbbon XML, e.g.
HTML Code:

XML: <tab id="rbnTestTab" label="Test Tab" getKeytip = "getKeytip">


VBA: Public Sub GetKeytip(control As IRibbonControl, ByRef label)
'get keytip function sets the keytip of a control according to its ID
'take the value stored on the worksheet
label = CStr(wksKeyTip.Range("rngKeytip").Value)
End Sub

I have a macro which gets the user's value from an InputBox, pastes it into a named range on a sheet, and then uses the .Invalidate and .InvalidateControl on the Ribbon object and the control "rbnTestTab" respectively, therefore calling the GetKeytip for the tab and setting the keytip.

This code works fine in Excel 2007, but isn't working at all in Excel 2010, does anyone have any suggestions why not? I can't figure it out and there isn't anything in the usual places (Ron de Bruin's site, Ken Puls' blog, Msdn, here!) mentioning any compatibility issues between Ribbon XML in 2007-2010.

Two additional notes which may help (or further confuse!):
1. The VBA callback is different between 2007-2010 (ByRef argument has different name); changing the argument to be called "returnedVal" stops the code working in 2007, but doesn't make it work in 2010, which is odd!:

VBA: Sub GetKeytip (control As IRibbonControl, ByRef label)
VBA: Sub GetKeytip(control As IRibbonControl, ByRef returnedVal)

2. If you use this code and try to set a keytip which is already assigned, e.g. "H" (Home tab), then the getKeytip fails and you get "Y" or "Y1" as the keytip on your tab, and this then cannot be changed to anything else after that by rerunning the macro/invalidate routines - you have to restart the workbook.

Any help greatly appreciated!

Hi, I've created my gallery where user can choose my color (I need more colors than the themes offers) and I use a label of each item like RGB value.
My question: How can I get label of item in VBA callbacks?

Look at Ribbon's code bellow (I had to put it as image)

and here is my code in VBA


Dim mIRibb  As IRibbonUI
'Callback for customUI.******
Sub idc_LoadRibbon(ribbon As IRibbonUI)
    Set mIRibb = ribbon
End Sub

'Callback for idc_galColor getItemLabel
Sub idc_GetLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
    MsgBox "Control Id = " & & vbNewLine & _
           "Index      = " & index & vbNewLine & _
           "ReturnVal  = " & returnedVal, vbInformation, "GetItemLabel"
End Sub

'Callback for idc_galColor onAction
Sub idc_Color_Click(control As IRibbonControl, id As String, index As Integer)
    MsgBox "Control Id = " & & vbNewLine & _
           "ID         = " & id & vbNewLine & _
           "Index      = " & index, vbInformation, "OnAction"
End Sub

Because I need to work with number which are stored in label property I'd like to know how Can I get the label property of selected item.
Of course, I don't want to show to user label property - because it is strange for them .

Hello all

Trying to create a simple addin to show my custom tab ..............

Tough addin is loading succesfully without error but not showing my tab.......

Addin has a simple function which is working properly but only not showing tab.....

Heres a XML

HTML Code:

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="" >
      <tab id="mrbrave" label = "mrbrave" insertBeforeMso="TabHome">
        <group id="ColorText" label="ColorText">
		<button id = "mrbraveb1" label = "ColorText" size = "large" onAction = "callColorText"/>

and my vba code


sub callColorText(control as iRibbonControl)
activecell.Font.ColorIndex = 4
end sub

function lenof(r as range)
lenof = len(r.value)
end function

please help me out.......


I'm trying to assign a macro to a button within a customized Ribbon. When the following code is copied into a Module within the active Workbook, the button works fine. However, when the same code is copied to a module within Personal.XLSB, I get the following error message in Excel:

"Cannot run the macro 'rxbtnTest_Click'. The macro may not be available in this workbook or all macros may be disabled"

Macro Settings is set to "Enable all macros". Basically, I'd like to use macros saved in Personal.XLSB rather than have to rely on macros saved in the current workbook only.

Here is the text from the UI Editor:

<group id="Navigation"
<button id="rxbtnTest"

VBA Code for Macro "Test"

'Callback for rxbtnTest onAction
Sub rxbtnTest_Click(control As IRibbonControl)
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
Selection.NumberFormat = "$#,##0"
End Sub

I have a Excel 2010 workbook that contains a custom ribbon. I have created the appropriate XML code to present the ribbon just as I want. The ribbon has one 'toggleButton' control that shows/hides the formula bar. This control was initially just a 'button' control and worked perfect, but when I made it a 'toggleButton' control i got 'Wrong number of arguments or invalid property assignment error'. I prefer a 'toggleButton' so it will show the stat of the control. My code is below. Can anyone help me diagnose this problem?


 'Callback for ShowHideFormBar onAction
 Sub callackShowHideFormBar(control As IRibbonControl)

'  Sub callackShowHideFormBar(control As IRibbonControl)
    Application.ScreenUpdating = False
    Select Case control.ID
    Case "ShowHideFormBar"
    If Not Application.DisplayFormulaBar Then
      bStateShowHideFormBar = False
    Application.DisplayFormulaBar = True
      bStateShowHideFormBar = True
      Application.DisplayFormulaBar = False
    End If
    myRibbon.InvalidateControl ("ShowHideFormBar")
End Select
End Sub

After several days of searching this site and many others, I Think I'm on my to understanding the basics of creating my own custom ribbon menu. I found the "CustomUIEditor" and Andy Pope's RibbonX Visual Designer. Both great tools.

I've created my menu. It has a new tab, two group, and some controls. My task is to load strings into one of the comboboxes from a list on a sheet. The id of the combobox is cmbo_ReachesCombo. I grabbed some code from Andy Pope's site that changes the text for a editbox. I tried to adapt it to my combobox, but it didn't work. Can anyone help with specific code for loading a combobox on the ribbon?


This is the xml

<        ribbon         >
<        tabs         >
<        tab 
        id        =     "     TSTab     "
     label        =     "     Time Series     "
     tag        =     "     TS     "     >
<        group 
        id        =     "     grpNavigate     "
     imageMso        =     "     UpgradeWorkbook     "
     label        =     "     Navigate     "
     tag        =     "     TS     "     >
<        comboBox 
        id        =     "     cmbo_ReachesCombo     "
     imageMso        =     "     DiagramReverseClassic     "
     label        =     "     Goto Reaches     "
     screentip        =     "     Select a Reach Page to Show     "
     tag        =     "     TS     "
     getText        =     "     cmbo_ReachesCombo_getText     "
     onChange        =     "     cmbo_ReachesCombo_onChange     "     />
<        button 
        id        =     "     btn_FlowSum     "
     imageMso        =     "     ReturnToTaskList     "
     label        =     "     Flow Summary     "
     screentip        =     "     Show the Flow Summary Page     "
     tag        =     "     TS     "
     onAction        =     "     btn_FlowSum_onAction     "     />
<        button 
        id        =     "     btn_SpeciesList     "
     imageMso        =     "     DatasheetNewField     "
     label        =     "     Species List     "
     screentip        =     "     Show the Species List Page     "
     tag        =     "     TS     "
     onAction        =     "     btn_SpeciesList_onAction     "     />

<        group 
        id        =     "     grpOptions     "
     imageMso        =     "     PageBreakInsertOrRemove     "
     label        =     "     Split Screen Options     "
     tag        =     "     TS     "     >
<        comboBox 
        id        =     "     cmbo_OptionList     "
     imageMso        =     "     DatasheetView     "
     label        =     "     Option List     "
     screentip        =     "     Choose an Option to Show     "     />
<        button 
        id        =     "     btn_OptionsOff     "
     imageMso        =     "     ErrorChecking     "
     label        =     "     Turn off Options     "
     screentip        =     "     Turn off     "
     onAction        =     "     btn_OptionsOff_onAction     "     />



    Public Sub cmbo_ReachesCombo_getText(control As IRibbonControl, ByRef returnedVal)
' Code for getText callback. Ribbon control Reaches Combobox
  Dim R As Range
  Set R = Sheets("Options").Range(Range("ReachPagesHeader").Offset(1, 0), Range("ReachPagesHeader").Offset(20, 0).End(xlUp))
    returnedVal = R.text
End Sub    

I set a new toggleButton to Excel ribbon:

Everything else works fine but not the label function.

Sub GetLabel(ByVal control As IRibbonControl, ByRef returnedVal)
       Select Case control.ID
        Case "rxblockmacros"
         If bLabelState Then
          returnedVal = "Activate macros"
          returnedVal = "Block macros"
        End If
      Case Else
     ' do nothing
     End Select

I have tried tons of various ways, but it just doesn't change the text of the toggleButton. When I open that particular document, button will get the label from this function, depending on how I have set bLabelState.

Option Explicit
Public grxIRibbonUI As IRibbonUI
Private bLabelState As Boolean
'Callback for customUI.******
Sub rxIRibbonUI_******(ribbon As IRibbonUI)
      Set grxIRibbonUI = ribbon
      bLabelState = True
End Sub

Any idea what I could try?

I have customized a new tab on the ribbon, incorporating Ron de Bruin's dropdown XML and Vb code and it does work - I can select repeatedly from the dropdown and it takes the user choice and puts in cell B10. The info in B10 is used in a lookup with results in L10. The info in cell L10 is used by another macro that initiates a user form.
Here is the problem - I can continue selecting from the dropdown with no problems UNTIL I run the macro that launches the user form that uses the info in L10. Once I have run that macro/userform I get an error in selecting from the dropdown
Here is the dropdown Vb


Option Explicit

Dim ItemCount As Integer
Dim ListItemsRg As Range
Dim MySelectedItem As String

''=========Drop Down Code =========

''Callback for Dropdown getItemCount.
''Tells Excel how many items in the drop down.
Sub DDItemCount(control As IRibbonControl, ByRef returnedVal)
    With Worksheets("Actual").Range("C15:C121")
        Set ListItemsRg = Range(.Cells(1), .Offset(.Rows.Count).End(xlUp))
        ItemCount = ListItemsRg.Rows.Count
        returnedVal = ItemCount
    End With
End Sub

''Callback for dropdown getItemLabel.
''Called once for each item in drop down.
''If DDItemCount tells Excel there are 10 items in the drop down
''Excel calls this sub 10 times with an increased "index" argument each time.
''We use "index" to know which item to return to Excel.
Sub DDListItem(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = ListItemsRg.Cells(index + 1).Value
    ''index is 0-based, our list is 1-based so we add 1.
End Sub

''Drop down change handler.
''Called when a drop down item is selected.
Sub DDOnAction(control As IRibbonControl, ID As String, index As Integer)
' Two ways to set the variable MySelectedItem to the dropdown value

'way 1
    MySelectedItem = ListItemsRg.Cells(index + 1).Value

    ''way 2
    'Call DDListItem(control, index, MySelectedItem)
Worksheets("Actual").Range("B10").Value = MySelectedItem
End Sub

''Returns index of item to display.
Sub DDItemSelectedIndex(control As IRibbonControl, ByRef returnedVal)
    'returnedVal = 0
    MySelectedItem = ListItemsRg.Cells(1).Value

End Sub

''------- End DD Code --------

Here is the "other" macro and userform (I have used ***** in this post to separate)


''Show the variable MySelectedItem (selected item in the dropdown)
''You can use this variable also in other macros
Sub ValueSelectedItem(control As IRibbonControl)
    ' MsgBox "The variable MySelectedItem have the value = " & MySelectedItem & vbNewLine & _
           ' "You can use MySelectedItem in other code now to use the dropdown value"
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1.Text = CStr(ThisWorkbook.Sheets("Actual").Range("L10").Value)
Me.TextBox2.Text = CStr(ThisWorkbook.Sheets("Actual").Range("C11").Value)

End Sub

Private Sub CommandButton1_Click()
End Sub

Private Sub CommandButton2_Click()

End Sub

By no stretch of the imagination am I good at Vb but have got this far
So the error I get is with the change handler for the dropdown
Error 91, Object Variable or With Block Variable not set
I have struggled for a couple of days (and nights) to solve this but am at a loss at to why the dropdown will continue to work after running every other macro but the one that uses related information. Any help would be GREATLY appreciated!
thanks much
PS this is a very large file and doubt I can email or attach

Below is a customUI script. There is a problem with the customGroup2 script. If I delete the code for customGroup2 it works. Where is the mistake? I can't find it.


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<customUI xmlns="">

	<tab id="customTab1" label="Detlef's stuff" insertAfterMso="TabAcrobat">

		<group id="customGroup1" label="Author Analysis stuff" insertAfterMso="GroupEditingExcel">
			<button id="customButton1" label="Find duplicate" size="large" onAction="Duplicate" imageMso="DefaultView" />
			<button id="customButton2" label="XMLtoCSV" size="large" onAction="XMLtoCSV" imageMso="FieldChooser" />
			<button id="customButton3" label="Consolidate" size="large" onAction="Consol" imageMso="ConditionalFormattingHighlightTextContaining" />
			<button id="customButton4" label="Author Analysis" size="large" onAction="AuthorAnalysis" imageMso="WhatIfAnalysisMenu" />

		<group id="customGroup2" label="Other stuff" insertAfterMso="customGroup1">
			<button id="customButton1" label="GetLink" size="large" onAction="GetHyperlink" imageMso="HyperlinkInsert" />



What is the best way to xml text on this forum??? When I try it messes up. Random!!!

HTML Code:

 <ribbon >
  <tabs >
    label="Time Series"
      label="Navigate to:"
      screentip="Navigate to other sheets"
    </group >
     label="Split Screen Options"
      label="Option List"
      screentip="Choose an Option to Show"
      sizeString="Option 99"
      label="Turn off Options"
      screentip="Turn off Time Series Options"
    </group >
   </tab >
  </tabs >
 </ribbon >
</customUI >

How do I update a label on a custom ribbon from VBA?

I added a label to a custom ribbon where I want to display a date. My model is set to run in a loop for a number of days. As it loops I want the date of the day it is running to be displayed on the custom ribbon. The book I have shows how to add the label control to the ribbon, shows a callback procedure called getLabel1 with the following code:

Please Login or Register  to view this content.

This will then display the system date on the ribbon but how do I go about to display a date of my own on the ribbon from within my loop in VBA? I don't know what to pass as parameters to the sub.

And can I add a icon of a calendar above the date well?


A macro called from my custom ribbon is firing twice and does not stop at break points. Even when I simplify the code to just be a msgbox it fires twice. When running code from the vba editor it works fine. This is Excel 2010.

Ribbon Code:
HTML Code:

<customui xmlns="">
            <tab id="customTab" label="Monthly" insertaftermso="Developer">
                <group id="customGroup" label="Montly">
                    <button id="customButton1" label="Create Instructor Summary" size="large" onaction="modUtilities.CreateNewMonthly()">


I have this code that should update textPERC everytime i exit another Textbox...


Sub TextMOTid_exit(ByVal Cancel As MSForms.ReturnBoolean)
Application.Run = percupdate()
End Sub

Sub percupdate()
 'UserFORM Percentage

    With textPERC
        'Set the text of the label.
             textPERC.Caption = Format(expression:=Sheet1.Range("A3"), Format:="0.00%")
        'Automatically size the label control.
             .AutoSize = True
             .WordWrap = False
        'Set the font used by the Label control.
             .Font.Name = "Tahoma"
    End With
End Sub

However on exit i get the following error...
'Compile Error:
Expected Function or Variable'

Probably something simple!!


I wanted to add a new button with Macro to the existing Excel Ribbon. For this case, I wanted the buttn to be inside the "Formulas" tab and inside the "Calculation" group. Using the Custom UI Editor, I wrote the following:


<customUI xmlns="">

      <tab idMso="TabFormulas" >
        <group id="GroupCalculation" label="Calculation"   >
          <button id="CalculateWB" label="Calculate Workbook" size="normal" onAction="CalcWB" imageMso="AddressBook" />



It doesn't insert the button as I liked it to be or is it not possible to do so?

could anyone help me with this below macro?
whats wrong with it, i receive error massage for the red line


Dim i, k As Integer
Dim N, B As String
i = 1: k = 1

If Workbooks("download.xls").Worksheets("Label List").Cells(i + 2, 5).Value = 0 Then
MsgBox "Filtering finished"
GoTo line2


If "N" = Left(Workbooks("download.xls").Worksheets("Label List").Cells(i + 2, 5), 1) Or "B" = Left(Workbooks("download.xls").Worksheets("Label List").Cells(i + 2, 5), 1) Then
Workbooks("download.xls").Worksheets("Label List").Cells(i + 2, 1).Resize(1, 22).Copy
 Workbooks("label system_main control.xls").Worksheets("LGEIN").Cells(k + 2, 1).Paste 

k = k + 1
i = i + 1
GoTo line1

i = i + 1
GoTo line1
End If

End If

End Sub

error massage:
Run-Time error '438'

Object doesn't support this method

thank you

On my ribbon, I have group that contains a button that opens a Calendar form and a label which will display a date. I want the user to select a date from the calendar which will then in turn update the label with the selected date. However, I'm not sure how to update the label with the user's selected date.

I'm new to Excel VBA and to the RibbonX.

I appreciate anyone's help on how to approach this problem.


I am hoping someone can help me out here.
I have a userform with over 90 labels, the label(name) is somthing like label60_10, label60_130, label 42_10
the userform background is a picture of a machine, each label in the userform represents a conveyor-belt.
the code in the userform is (90 sub's) somthing like this.

Private Sub Label60_130_Click()
Link = "G:\GT\TOD\Object doc\Sorter\item 60_130.pdf"
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
End Sub

the link open's a pdf with the machine specs.

To reduce the code i am looking for is a code "loop" that if i click on a label in the userform, it returns the label(name) so i can "build" the pdf name e.g. label60_130 --> "item 60_130.pdf"

I hope someone can help me..
thanks Tonnie

So I am trying to call my macro from another macro and ever since i added
control As IRibbonControl i no longer able to called from another macros
Does anyone know how to fix this problem
Thank you for your the help!!

Sub DeleteBlankRows(control As IRibbonControl)
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range("G2:G" & LastRow).Select
On Error Resume Next
End Sub

Does anyone know if it is at all possible to figure out in VBA which label is associated with which control? For example, when I create a label by itself on a form, Access asks me if I want to associate that label with a control through a contextual error message. If I select a control, the error is cleared and the controls are "linked" together. Does Access store this link somewhere I can get to programmatically?


I am hoping someone can help me out here.
I have a userform with over 90 labels, the label(name) is somthing like label60_10, label60_130, label 42_10
the userform background is a picture of a machine, each label in the userform represents a conveyor-belt.
the code in the userform is somthing like this.

Private Sub Label60_130_Click()
Link = "G:\GT\TOD\Object doc\Sorter\item 60_130.pdf"
ActiveWorkbook.FollowHyperlink Address:=Link, NewWindow:=True
End Sub

the link open's a pdf with the machine specs.

what i am looking for is a code "loop" that if i click on a label in the userform, it returns the label(name) so i can "build" the pdf name e.g. label60_130 --> "item 60_130.pdf"

I hope someone can help me..
thanks Tonnie

Good morning,

I would like to improve the VBA code I am currently working on, being new at this, I thought I would ask the experts.
My goal is to increment a number typed in my A1 cell by 1 all the way to I47.

Here is what I did so far which is a button which adds 1 to A1, then adds 2 to A1, then adds 3 to A1, etc.... You will notice it is very typo prone and I would like to avoid that, your help would be greatly appreciated.

Thank you,



Sub Button2_Click()
Sheets("sheet1").Range("a1").Value = Sheets("sheet1").Range("a1").Value + 1
Sheets("sheet1").Range("a2").Value = Sheets("sheet1").Range("a1").Value + 1
Sheets("sheet1").Range("a3").Value = Sheets("sheet1").Range("a1").Value + 2
Sheets("sheet1").Range("a4").Value = Sheets("sheet1").Range("a1").Value + 3
Sheets("sheet1").Range("a5").Value = Sheets("sheet1").Range("a1").Value + 4
Sheets("sheet1").Range("a6").Value = Sheets("sheet1").Range("a1").Value + 5
Sheets("sheet1").Range("a7").Value = Sheets("sheet1").Range("a1").Value + 6
Sheets("sheet1").Range("a8").Value = Sheets("sheet1").Range("a1").Value + 7
Sheets("sheet1").Range("a9").Value = Sheets("sheet1").Range("a1").Value + 8
Sheets("sheet1").Range("a10").Value = Sheets("sheet1").Range("a1").Value + 9
Sheets("sheet1").Range("a11").Value = Sheets("sheet1").Range("a1").Value + 10
Sheets("sheet1").Range("a12").Value = Sheets("sheet1").Range("a1").Value + 11
Sheets("sheet1").Range("a13").Value = Sheets("sheet1").Range("a1").Value + 12
Sheets("sheet1").Range("a14").Value = Sheets("sheet1").Range("a1").Value + 13
Sheets("sheet1").Range("a15").Value = Sheets("sheet1").Range("a1").Value + 14
Sheets("sheet1").Range("a16").Value = Sheets("sheet1").Range("a1").Value + 15
Sheets("sheet1").Range("a17").Value = Sheets("sheet1").Range("a1").Value + 16
Sheets("sheet1").Range("a18").Value = Sheets("sheet1").Range("a1").Value + 17
Sheets("sheet1").Range("a19").Value = Sheets("sheet1").Range("a1").Value + 18
Sheets("sheet1").Range("a20").Value = Sheets("sheet1").Range("a1").Value + 19
Sheets("sheet1").Range("a21").Value = Sheets("sheet1").Range("a1").Value + 20
Sheets("sheet1").Range("a22").Value = Sheets("sheet1").Range("a1").Value + 21
Sheets("sheet1").Range("a23").Value = Sheets("sheet1").Range("a1").Value + 22
Sheets("sheet1").Range("a24").Value = Sheets("sheet1").Range("a1").Value + 23
Sheets("sheet1").Range("a25").Value = Sheets("sheet1").Range("a1").Value + 24
Sheets("sheet1").Range("a26").Value = Sheets("sheet1").Range("a1").Value + 25
Sheets("sheet1").Range("a27").Value = Sheets("sheet1").Range("a1").Value + 26
Sheets("sheet1").Range("a28").Value = Sheets("sheet1").Range("a1").Value + 27
Sheets("sheet1").Range("a29").Value = Sheets("sheet1").Range("a1").Value + 28
Sheets("sheet1").Range("a30").Value = Sheets("sheet1").Range("a1").Value + 29
Sheets("sheet1").Range("a31").Value = Sheets("sheet1").Range("a1").Value + 30
Sheets("sheet1").Range("a32").Value = Sheets("sheet1").Range("a1").Value + 31
Sheets("sheet1").Range("a33").Value = Sheets("sheet1").Range("a1").Value + 32
Sheets("sheet1").Range("a34").Value = Sheets("sheet1").Range("a1").Value + 33
Sheets("sheet1").Range("a35").Value = Sheets("sheet1").Range("a1").Value + 34
Sheets("sheet1").Range("a36").Value = Sheets("sheet1").Range("a1").Value + 35
Sheets("sheet1").Range("a37").Value = Sheets("sheet1").Range("a1").Value + 36
Sheets("sheet1").Range("a38").Value = Sheets("sheet1").Range("a1").Value + 37
Sheets("sheet1").Range("a39").Value = Sheets("sheet1").Range("a1").Value + 38
Sheets("sheet1").Range("a40").Value = Sheets("sheet1").Range("a1").Value + 39
Sheets("sheet1").Range("a41").Value = Sheets("sheet1").Range("a1").Value + 40
Sheets("sheet1").Range("a42").Value = Sheets("sheet1").Range("a1").Value + 41
Sheets("sheet1").Range("a43").Value = Sheets("sheet1").Range("a1").Value + 42
Sheets("sheet1").Range("a44").Value = Sheets("sheet1").Range("a1").Value + 43
Sheets("sheet1").Range("a45").Value = Sheets("sheet1").Range("a1").Value + 44
Sheets("sheet1").Range("a46").Value = Sheets("sheet1").Range("a1").Value + 45
Sheets("sheet1").Range("a47").Value = Sheets("sheet1").Range("a1").Value + 46
'b column
Sheets("sheet1").Range("b1").Value = Sheets("sheet1").Range("a1").Value + 47
Sheets("sheet1").Range("b2").Value = Sheets("sheet1").Range("a1").Value + 48
Sheets("sheet1").Range("b3").Value = Sheets("sheet1").Range("a1").Value + 49
Sheets("sheet1").Range("b4").Value = Sheets("sheet1").Range("a1").Value + 50
Sheets("sheet1").Range("b5").Value = Sheets("sheet1").Range("a1").Value + 51
Sheets("sheet1").Range("b6").Value = Sheets("sheet1").Range("a1").Value + 52
Sheets("sheet1").Range("b7").Value = Sheets("sheet1").Range("a1").Value + 53
Sheets("sheet1").Range("b8").Value = Sheets("sheet1").Range("a1").Value + 54
Sheets("sheet1").Range("b9").Value = Sheets("sheet1").Range("a1").Value + 55
Sheets("sheet1").Range("b10").Value = Sheets("sheet1").Range("a1").Value + 56
Sheets("sheet1").Range("b11").Value = Sheets("sheet1").Range("a1").Value + 57
Sheets("sheet1").Range("b12").Value = Sheets("sheet1").Range("a1").Value + 58
Sheets("sheet1").Range("b13").Value = Sheets("sheet1").Range("a1").Value + 59
Sheets("sheet1").Range("b14").Value = Sheets("sheet1").Range("a1").Value + 60
Sheets("sheet1").Range("b15").Value = Sheets("sheet1").Range("a1").Value + 61
Sheets("sheet1").Range("b16").Value = Sheets("sheet1").Range("a1").Value + 62
Sheets("sheet1").Range("b17").Value = Sheets("sheet1").Range("a1").Value + 63
Sheets("sheet1").Range("b18").Value = Sheets("sheet1").Range("a1").Value + 64
Sheets("sheet1").Range("b19").Value = Sheets("sheet1").Range("a1").Value + 65
Sheets("sheet1").Range("b20").Value = Sheets("sheet1").Range("a1").Value + 66
Sheets("sheet1").Range("b21").Value = Sheets("sheet1").Range("a1").Value + 67
Sheets("sheet1").Range("b22").Value = Sheets("sheet1").Range("a1").Value + 68
Sheets("sheet1").Range("b23").Value = Sheets("sheet1").Range("a1").Value + 69
Sheets("sheet1").Range("b24").Value = Sheets("sheet1").Range("a1").Value + 70
Sheets("sheet1").Range("b25").Value = Sheets("sheet1").Range("a1").Value + 71
Sheets("sheet1").Range("b26").Value = Sheets("sheet1").Range("a1").Value + 72
Sheets("sheet1").Range("b27").Value = Sheets("sheet1").Range("a1").Value + 73
Sheets("sheet1").Range("b28").Value = Sheets("sheet1").Range("a1").Value + 74
Sheets("sheet1").Range("b29").Value = Sheets("sheet1").Range("a1").Value + 75
Sheets("sheet1").Range("b30").Value = Sheets("sheet1").Range("a1").Value + 76
Sheets("sheet1").Range("b31").Value = Sheets("sheet1").Range("a1").Value + 77
Sheets("sheet1").Range("b32").Value = Sheets("sheet1").Range("a1").Value + 78
Sheets("sheet1").Range("b33").Value = Sheets("sheet1").Range("a1").Value + 79
Sheets("sheet1").Range("b34").Value = Sheets("sheet1").Range("a1").Value + 80
Sheets("sheet1").Range("b35").Value = Sheets("sheet1").Range("a1").Value + 81
Sheets("sheet1").Range("b36").Value = Sheets("sheet1").Range("a1").Value + 82
Sheets("sheet1").Range("b37").Value = Sheets("sheet1").Range("a1").Value + 83
Sheets("sheet1").Range("b38").Value = Sheets("sheet1").Range("a1").Value + 84
Sheets("sheet1").Range("b39").Value = Sheets("sheet1").Range("a1").Value + 85
Sheets("sheet1").Range("b40").Value = Sheets("sheet1").Range("a1").Value + 86
Sheets("sheet1").Range("b41").Value = Sheets("sheet1").Range("a1").Value + 87
Sheets("sheet1").Range("b42").Value = Sheets("sheet1").Range("a1").Value + 88
Sheets("sheet1").Range("b43").Value = Sheets("sheet1").Range("a1").Value + 89
Sheets("sheet1").Range("b44").Value = Sheets("sheet1").Range("a1").Value + 90
Sheets("sheet1").Range("b45").Value = Sheets("sheet1").Range("a1").Value + 91
Sheets("sheet1").Range("b46").Value = Sheets("sheet1").Range("a1").Value + 92
Sheets("sheet1").Range("b47").Value = Sheets("sheet1").Range("a1").Value + 93
'c column
Sheets("sheet1").Range("c1").Value = Sheets("sheet1").Range("a1").Value + 94
Sheets("sheet1").Range("c2").Value = Sheets("sheet1").Range("a1").Value + 95
Sheets("sheet1").Range("c3").Value = Sheets("sheet1").Range("a1").Value + 96
Sheets("sheet1").Range("c4").Value = Sheets("sheet1").Range("a1").Value + 97
Sheets("sheet1").Range("c5").Value = Sheets("sheet1").Range("a1").Value + 98
Sheets("sheet1").Range("c6").Value = Sheets("sheet1").Range("a1").Value + 99
Sheets("sheet1").Range("c7").Value = Sheets("sheet1").Range("a1").Value + 101
Sheets("sheet1").Range("c8").Value = Sheets("sheet1").Range("a1").Value + 102
Sheets("sheet1").Range("c9").Value = Sheets("sheet1").Range("a1").Value + 103
Sheets("sheet1").Range("c10").Value = Sheets("sheet1").Range("a1").Value + 104
Sheets("sheet1").Range("c11").Value = Sheets("sheet1").Range("a1").Value + 105
Sheets("sheet1").Range("c12").Value = Sheets("sheet1").Range("a1").Value + 106
Sheets("sheet1").Range("c13").Value = Sheets("sheet1").Range("a1").Value + 107
Sheets("sheet1").Range("c14").Value = Sheets("sheet1").Range("a1").Value + 108
Sheets("sheet1").Range("c15").Value = Sheets("sheet1").Range("a1").Value + 109
Sheets("sheet1").Range("c16").Value = Sheets("sheet1").Range("a1").Value + 110
Sheets("sheet1").Range("c17").Value = Sheets("sheet1").Range("a1").Value + 111
Sheets("sheet1").Range("c18").Value = Sheets("sheet1").Range("a1").Value + 112
Sheets("sheet1").Range("c19").Value = Sheets("sheet1").Range("a1").Value + 113
Sheets("sheet1").Range("c20").Value = Sheets("sheet1").Range("a1").Value + 114
Sheets("sheet1").Range("c21").Value = Sheets("sheet1").Range("a1").Value + 115
Sheets("sheet1").Range("c22").Value = Sheets("sheet1").Range("a1").Value + 116
Sheets("sheet1").Range("c23").Value = Sheets("sheet1").Range("a1").Value + 117
Sheets("sheet1").Range("c24").Value = Sheets("sheet1").Range("a1").Value + 118
Sheets("sheet1").Range("c25").Value = Sheets("sheet1").Range("a1").Value + 119
Sheets("sheet1").Range("c26").Value = Sheets("sheet1").Range("a1").Value + 120
Sheets("sheet1").Range("c27").Value = Sheets("sheet1").Range("a1").Value + 123
Sheets("sheet1").Range("c28").Value = Sheets("sheet1").Range("a1").Value + 124
Sheets("sheet1").Range("c29").Value = Sheets("sheet1").Range("a1").Value + 125
Sheets("sheet1").Range("c30").Value = Sheets("sheet1").Range("a1").Value + 126
Sheets("sheet1").Range("c31").Value = Sheets("sheet1").Range("a1").Value + 127
Sheets("sheet1").Range("c32").Value = Sheets("sheet1").Range("a1").Value + 128
Sheets("sheet1").Range("c33").Value = Sheets("sheet1").Range("a1").Value + 129
Sheets("sheet1").Range("c34").Value = Sheets("sheet1").Range("a1").Value + 130
Sheets("sheet1").Range("c35").Value = Sheets("sheet1").Range("a1").Value + 131
Sheets("sheet1").Range("c36").Value = Sheets("sheet1").Range("a1").Value + 132
Sheets("sheet1").Range("c37").Value = Sheets("sheet1").Range("a1").Value + 133
Sheets("sheet1").Range("c38").Value = Sheets("sheet1").Range("a1").Value + 134
Sheets("sheet1").Range("c39").Value = Sheets("sheet1").Range("a1").Value + 135
Sheets("sheet1").Range("c40").Value = Sheets("sheet1").Range("a1").Value + 136
Sheets("sheet1").Range("c41").Value = Sheets("sheet1").Range("a1").Value + 137
Sheets("sheet1").Range("c42").Value = Sheets("sheet1").Range("a1").Value + 138
Sheets("sheet1").Range("c43").Value = Sheets("sheet1").Range("a1").Value + 139
Sheets("sheet1").Range("c44").Value = Sheets("sheet1").Range("a1").Value + 140
Sheets("sheet1").Range("c45").Value = Sheets("sheet1").Range("a1").Value + 141
Sheets("sheet1").Range("c46").Value = Sheets("sheet1").Range("a1").Value + 142
Sheets("sheet1").Range("c47").Value = Sheets("sheet1").Range("a1").Value + 143
End Sub

I have a graph and 2 textboxs which I want to change dynamically. Now this should be really easy but I cant seem to find the name of the boxes. If I hover the box it says "TextBox1" for both of them. I tried changing the text in code


TextBox1.text = "hi" 
[TextBox1] = "hi"

neither of which work.

Ive also brought up the Property page from the developer tab, it only displays "Graph1" as the only object on there.

Lastly Ive tried which doesnt work:


Selection.Characters.Text = "hi"

Ive placed a new label on this page as well, "Label 73" which I can change dynamically using the above code (modified).

Any help would be appreciated.


hi all,

I have a userform with a command button on it.
This button kicks of a series of tasks, and I was hoping to update the caption of a label underneath the button, so that the user can see what is currently being processed.
here is my code:

Private Sub RefreshButton_Click()

Application.ScreenUpdating = false
UserForm1.Label2.Caption = "Refreshing PET projects"
Sheets("All PETS Phase Select").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

UserForm1.Label2.Caption = "Refreshing Clarity Projects"
Sheets("Clarity Projects").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
UserForm1.Label2.Caption = "Refreshing FOB Teams"
Sheets("FOB Teams").Select
Selection.QueryTable.Refresh BackgroundQuery:=False

End Sub

all this does is nothing apart from AFTER it has refreshed the last query in FOB Teams, at which point the label caption is changed to Refreshing FOB Teams"

can anyone suggest what I am doing wrong?

thanks in advance,