Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:


Advertisements


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:
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 Video Tutorials

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







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.

Thanks in advance


Here is a part of CustomUI

HTML Code:

<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
	<tabs>
		<tab id="ID1" label="File1">
			<group id="groupID" label="Group">
				<button id="btnQuit" onAction="btnQuit_Click" label="Quit"/>
			</group>
		</tab>
	</tabs>
  </ribbon>
</customUI>


Here is the callback function

Code:

Private Sub btnQuit_Click(sender As IRibbonControl)
     ThisWorkbook.Close
End Sub


I also have more applications like this one where I have the same callback function defined.


If I have two or more files open and when I click Quick button it calls btnQuit_Click funtion but from the other file, closing wrong file.

Does anybody know how to force Excel to call correct callback function?

Thanks.


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.******
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 = " & 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 ..............

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



please help me out.......

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:

<group id="Navigation"
label="Navigation">
<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
.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?

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




    




Code:

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



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">
			<button id="customButton1" label="GetLink" size="large" onAction="GetHyperlink" imageMso="HyperlinkInsert" />
		</group>

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

</customUI>





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



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


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"
     imageMso="UpgradeWorkbook"
     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 >





I am having problems modifying the ribbon using ribbon x (through the custom UI).
I would like to be able to add additional buttons to the "add-ins" tab, however I can't seem to get it to work.
The ribbon x code I am using looks like this (fairly standard)
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon startFromScratch="false">
<tabs>
<tab idMso = "TabAddIns">
<group id="group1" label="Custom Macros">
<button id="Button1" label="Range Name" onAction="RangeName_Clickhandler" />
<button id="Button2" label="Paste Format" onAction="Custom_2_Clickhandler" />
<button id="Button3" label="Add Format" onAction="Custom_3_Clickhandler" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
what am I doing wrong?
I can get this to work when I apply this to a new (user created) tab in the ribbon.
Thanks
Dave


Hi,

I have created a new Add-In menu using XML <CustomUI>. The menu is created successfully along with buttons, controls, etc. I wanted a comboBox to display all styles within in. For this, I have written the code: <comboBox idMso="StyleGalleryClassic"/>. However, this comboBox is not shown. If I change the code to <gallery idMso="StyleGalleryClassic"/>, then the Style List is shown. But I want the list to be shown in comboBox. Can someone help me? I am using Excel 2007. I am also posting the code below:
Code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
      <tab id="rxFSExcelCustomTab" label="Add-Ins 2011">
        <group id="FSExcelGroup1" label="General">
          <button id="OnScreen" label="OnScreen Tool"  onAction="OnScreen_Error_Finding" />
        </group>

        <group id="FSExcelGroup2" label="FS Toolbar">
          <box id="myBox1">
            <button id="btnTlBarNewFS"  image="NewFS" screentip="Blank New FS Excel" onAction="btnTlBarProc_NewFS" />
            <button id="btnTlBarCopyFS"  image="Copy" screentip="Clear Format and Copy" onAction="btnTlBarProc_ClearFormatANDCopy" />
          </box>

          <box id="myBox2">
            <comboBox idMso="StyleGalleryClassic" label="Style:"/>	
          </box>
        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>


Thanks in advance,

Srinivas


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:
If Workbooks("download.xls").Worksheets("Label List").Cells(i + 2, 5).Value = 0 Then
MsgBox "Filtering finished"
GoTo line2

Else


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

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-


I have gone through a few programs designed to create custom UI ribbon's though I do not see an option for adding back the standard tabs. I would like to add back the text tab for instance.
This is a complation of the output from a few of the ribbon creation programs.
Code:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="OnRibbonLoad" loadImage="LoadImages">
    <commands> 
        <command idMso="FileNewDatabase" enabled="false"/>
        <command idMso="FileCloseDatabase" enabled="false"/> 
        <command idMso="ApplicationOptionsDialog" enabled="false"/>
        <command idMso="FileOpenDatabase" enabled="false"/>
        <command idMso="FileExit" enabled="false"/>
        <command idMso="Help" enabled="false"/>
    </commands>
  <ribbon startFromScratch="true">
        <officeMenu>  
            <control idMso="FileOpen" visible="false" />
            <control idMso="FileNew" visible="false" />
            <control idMso="FileNewDatabase" visible="false"/>
            <control idMso="SourceControlCreateDatabaseFromProject" visible="false"/>
            <control idMso="FileOpenDatabase" visible="false"/>
            <control idMso="FileSave" visible="false"/>
            <control idMso="ConvertDatabaseFormat" visible="false"/>
            <control idMso="FileSaveAsMenuAccess" visible="false"/>
            <control idMso="FileManageMenu" visible="false"/>
            <control idMso="FileSendAsAttachment" visible="false"/>
            <control idMso="MenuPublish" visible="false"/>
            <control idMso="FileServerMenu" visible="false"/>
            <control idMso="FileCloseDatabase" visible="false"/>
            <button idMso="FileCompactAndRepairDatabase" visible="false"/>
            <button idMso="ApplicationOptionsDialog" enabled="false"/>
            <button idMso="Help" enabled="false"/>
        </officeMenu>
    <tabs>
      <tab id="tabLetterAutomation" label="Letter Automation" getVisible ="GetVisible">
        <group id="grpPrint" label="Print" getVisible ="GetVisible">
          <button id="btnPrint" size="large" label="Print" imageMso="FilePrintQuick" tag="RibbonName:=;inMenu:=;CustomPictu =;CustomPicturePath:=" onAction="OnActionButton" getVisible="GetVisible" getEnabled="GetEnabled" />
          <button id="btnPreview" size="large" label="Preview" imageMso="FilePrintPreview" tag="RibbonName:=;inMenu:=;CustomPictu =;CustomPicturePath:=" onAction="OnActionButton" getVisible="GetVisible" getEnabled="GetEnabled" />
        </group>
        <group id="grpExit" label="Exit" getVisible ="GetVisible">
          <button id="btnClose" size="large" label="Close" imageMso="GroupColorModeClose" tag="RibbonName:=;inMenu:=;CustomPictu =;CustomPicturePath:=" onAction="OnActionButton" getVisible="GetVisible" getEnabled="GetEnabled" />

        </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>



Now part two.
Is it possible to remove the list of recently opened documents just for this file (withough killing the registry of them).
Or completly disable the use of the office buttion.
(as you can see most of this is removing or hiding the items that reside in the office buttion).

Any advice would help.

Thankyou.


Hi,

first time poster so sorry if I posted in the wrong place, I need some help with a macro I am writing for work.

I have created a userform for which I have a label to define the message to the user of the form. This label is defined by certain text in various cells on the excel form it is modifying. I would like to make specific words in the label bold.

When I have tried to do this, I get the whole label as bold which I don't want as I wish to highlight some key words.

could somebody offer some insight please?

Code I have included below....
Code:

With ComboBox1
    .AddItem "1"
    .AddItem "2"
    .AddItem "3"
    .AddItem "4"
    .AddItem "5"
    .AddItem "6"
    .AddItem "7"
    .AddItem "8"
    .AddItem "9"
    .AddItem "10"
    End With
If Range("S1") > 0 Then GoTo 55
Range("S1") = 0
55
glert = Range("S1") + 1
Range("S1") = glert

If glert = 1 Then
feat = Range("A12").Value
fail = Range("B12").Value
eff = Range("C12").Value
Else
If glert = 2 Then
feat = Range("A47").Value
fail = Range("B47").Value
eff = Range("C47").Value
Else
If glert = 3 Then
feat = Range("A82").Value
fail = Range("B82").Value
eff = Range("C82").Value
Else
If glert = 4 Then
feat = Range("A82").Value
fail = Range("B94").Value
eff = Range("C82").Value
Else
If glert = 5 Then
feat = Range("A120").Value
fail = Range("B120").Value
eff = Range("C120").Value
Else
If glert = 6 Then
feat = Range("A120").Value
fail = Range("B138").Value
eff = Range("C138").Value
Else
End If
End If
End If
End If
End If
End If

'_____________________________________________________________________________________
form_severity1.Label1.Caption = "For the feature" & " " & """" & feat & """" & ", if the function fails for being" & " " & fail & " " & ", what would the severity be?" & "  " & "The effect of the failure would be - " & " " & eff
form_severity1.Label1.FontSize = 12


I wish to have 'eff' , 'feat' and 'fail' bold. As you can see these vary depending on where the macro is at any certain stage.

Thanks

Phillip


Hi, im missing a very important link when trying to automate a process between two workbooks.. im sure ive made this work before, but i cant find the example i was working on.

Basically this is the process.

1) User is in workbook "Master Sheet"
2) updates are made in this workbook by the user
3) a button is hit in the workbook and this fires off a bit of automation code
4) Workbook "Control" is opened and updated with values from "Master Sheet"
5) Workbook "Control" is saved and closed.


Im ok opening "Control" from the button code, but im getting mixed up in my references between the two open sheets:

Code:

Dim controlss As Workbook, controlwb As Worksheet, controlstr As String

controlstr = "\\cashrec\imports\Control.xls"
Set wbopen = Workbooks.Open(controlstr)
    Application.WindowState = xlMinimized
  
Dim ControlLastRow As Integer
ControlLastRow = wbopen.ActiveSheet.Range("B65536").End(xlUp).Row

'wbopen.ActiveSheet.Range("B" & ControlLastRow + 1).Value = currentbook.Worksheet("MasterControl").Range("C7")


this code opens control, finds the last used row and is then ready to insert a value from "master sheet". I can insert a constant value in to a field within "Control", but i cant work out how to call back control to my "Master sheet" workbook that is still open.

Do i need to declare somewhere earlier in my code what the current book is called before focus moves to the workbook opened by the code above??

the code i will be using to insert a value in to "Control" is below:

Code:

'wbopen.ActiveSheet.Range("B" & ControlLastRow + 1).Value = currentbook.Worksheet("MasterControl").Range("C7")


the bold text is the bit that i am stuck on - hwo to call back to the "master sheet".

Workbook "master sheet" contains one sheet "Mastercontrol"
Workbook "Control" contains one sheet "control"

Thanks for any help!!!


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.


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?


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







Good day,

Is there a more condensed, more efficient way to execute the alphabetizing a column in Excel? This is what I got from the VBA Recorder.

Code:

Sheets(2).Select
Sheets(2).Range("F2:F231").Select
    ActiveWorkbook.Worksheets("Sort & Label").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sort & Label").Sort.SortFields.Add Key:=Range("F2" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sort & Label").Sort
        .SetRange Range("F2:G231")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Thank you for your time and assistance