Email:      Pass:    Pass?

E-mail:

# Unwanted Code

Each time I open Excel and press [Alt] [F11] in a blank workbook, I have the following code:
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

## 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 ...

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

All,

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

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


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.

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">


Code:

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!:
Code:

VBA: Sub GetKeytip (control As IRibbonControl, ByRef label)
'2007, http://msdn.microsoft.com/en-us/library/aa722523(v=office.12).aspx
VBA: Sub GetKeytip(control As IRibbonControl, ByRef returnedVal)
'2010, http://msdn.microsoft.com/en-us/library/ee691833.aspx


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

Code:

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

'Callback for idc_galColor getItemLabel
Sub idc_GetLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
MsgBox "Control Id = " & 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 = " & 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 ..............

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="http://schemas.microsoft.com/office/2009/07/customui" >
<ribbon>
<tabs>
<tab id="mrbrave" label = "mrbrave" insertBeforeMso="TabHome">
<group id="ColorText" label="ColorText">
<button id = "mrbraveb1" label = "ColorText" size = "large" onAction = "callColorText"/>
</group>
</tab>
</tabs>
</ribbon>
</customUI>


and my vba code

Code:

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

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


Thanks

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:

<button id="rxbtnTest"
label="Test"
imageMso="CreateReportFromWizard"
onAction="rxbtnTest_Click"/>
</group>

VBA Code for Macro "Test"

'Callback for rxbtnTest onAction
Sub rxbtnTest_Click(control As IRibbonControl)
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
End With
With Selection.Font
.Color = -16776961
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?

Code:

 '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
Else
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?

Jeff

This is the xml
Code:

<        ribbon         >
<        tabs         >
<        tab
id        =     "     TSTab     "
label        =     "     Time Series     "
tag        =     "     TS     "     >
<        group
id        =     "     grpNavigate     "
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     "
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     "     />



Code:

    Public Sub cmbo_ReachesCombo_getText(control As IRibbonControl, ByRef returnedVal)
'
' Code for getText callback. Ribbon control Reaches Combobox
'
Dim R As Range

returnedVal = R.text

End Sub


I set a new toggleButton to Excel ribbon:

Everything else works fine but not the label function.
Code:

Sub GetLabel(ByVal control As IRibbonControl, ByRef returnedVal)
Select Case control.ID
Case "rxblockmacros"
If bLabelState Then
returnedVal = "Activate macros"
Else
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.
Code:

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

Code:

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.
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)

Code:

''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"
UserForm1.Show
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()
DataInput.Show
End
End Sub

Private Sub CommandButton2_Click()

End
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
Vonda
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.

Code:

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

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

<ribbon>
<tabs>
<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>

<group id="customGroup2" label="Other stuff" insertAfterMso="customGroup1">
</group>

</tab>
</tabs>
</ribbon>

</customUI>


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

HTML Code:

<customUI
xmlns="http://schemas.microsoft.com/office/2006/01/customui"
******="myRibbon_******">
<ribbon >
<tabs >
<tab
id="TSTab"
label="Time Series"
tag="TS">
<group
id="grpNavigate"
label="Navigate"
tag="TS">
<dropDown
id="drp_Navigate"
keytip="&amp;TN"
label="Navigate to:"
screentip="Navigate to other sheets"
sizeString="WWWWWWWWWWW"
tag="TS"
getItemCount="drp_Navigate_getItemCount"
getItemID="drp_Navigate_getItemID"
getItemLabel="drp_Navigate_getItemLabel"
getSelectedItemIndex="drp_Navigate_getSelectedItemIndex"
onAction="TS_Navigate_OnAction"/>
</group >
<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"
sizeString="Option 99"
tag="TS"/>
<button
id="btn_OptionsOff"
imageMso="ErrorChecking"
label="Turn off Options"
screentip="Turn off Time Series Options"
tag="TS"
onAction="btn_OptionsOff_onAction"/>
</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?

Hello,

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="http://schemas.microsoft.com/office/2009/07/customui">
<ribbon>
<tabs>
<tab id="customTab" label="Monthly" insertaftermso="Developer">
<group id="customGroup" label="Montly">
<button id="customButton1" label="Create Instructor Summary" size="large" onaction="modUtilities.CreateNewMonthly()">
</button></group>

</tab>
</tabs>
</ribbon>
</customui>


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

Code:

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!!

Hi,

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:

Code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">

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

</tabs>
</ribbon>

</customUI>


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

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

Code:

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

line1:
MsgBox "Filtering finished"
GoTo line2

Else

Workbooks("label system_main control.xls").Worksheets("LGEIN").Cells(k + 2, 1).Paste

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

Else
i = i + 1
GoTo line1
End If

line2:
End If

End Sub


error massage:
-------------------------------------------------
Run-Time error '438'

Object doesn't support this method
-------------------------------------------------

thank you
juna-

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.

Hi,

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()
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!!
Code:

Sub DeleteBlankRows(control As IRibbonControl)
LastRow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Range("G2:G" & LastRow).Select
On Error Resume Next
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
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?

Hi,

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()
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,

John

CODE:

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

Code:

TextBox1.text = "hi"
and
[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:

Code:

ActiveChart.Shapes("TextBox1").Select
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.

EDIT: Oh GAWD

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:
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?