Unwanted Code

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

Free Excel Help Forum

- Ask any question about Excel and have it answered in no time.

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

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

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

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

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

Hi All,

I'm trying to work out something that would automatically fill a DYMO label template and print the label. However, I noticed that this is way over my head... So if there is anybody out there who can help... please!

Attached file has a few sheets.
Sheet "WARR_FILL_IN" only contains fixed info which is transferred onto the label.
Sheet "Part" is the sheet where partnumbers and serialnumbers etc are scanned.
Sheet "DYMO label" contains the DYMO label layout (A1:E4) and some calculation explanations (G1:L3).

What needs to happen:
1. People are scanning partnumbers and serialnumbers in the "Part" sheet.
2. When there are 60 scans (column C), the box is full and a DYMO label should be printed for this box.
3. Scanning continues and when another 60 scans are done, the 2nd label should be printed (can be different partnumber).
4. A re-print option for a label should be possible via a re-print button

On the "DYMO label" sheet, I've explained where the data is coming from and also how the 2nd label should look like, based on the scanned examples in the "Part" sheet.
The DYMO printer is a DYMO LabelWriter 400 Turbo.

I'm not even sure if the above is possible in the first place. I would really appreciate it if somebody is able to shine some light on this, as I'm completely stuck...

In case of any questions, please do let me know.

DYMO file.xlsm
Thanks as always!

Good morning gang. I am trying to write a macro that will create a command button on a sheet and assign VBA code to that button. Basically, my original code creates a new sheet. I would like that new sheet to have button on it that will, when clicked, run another macro.

Here's what I have (of course it doesn't work as intended. The button is created, but the label isn't changed nor is the code assigned):


Sheets.Add.Move Befo =Sheets(1)
'more code follows...then>>>
ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=369, Top:=27.75, Width:=106.5, Height:= _
       Selection.Caption = "My Button"
       Selection.OnAction = "ThisWorkbook.MyCode Here"

Any ideas would be greatly appreciated. Thanks in advance!


Over the past couple of weeks I took it upon myself to learn how to create a custom ribbonX menu. I didn't find much information about the ribbon extensibilty while searching through the MrExcel message board. I found information to help me out from many different sites.

I have created many traditional commandbar menus in version 2003 through 2007. I still have a long way to go to learn how to interact with the controls on the ribbon, but I finally think I have the basics down.

I want to share the code and resources with all and I hope others could post to this with their resources that helped them.

The following code is an example of a custom UI ribbonX for excel. It shows a new tab, two groups and a few controls. The main focus is a dropdown box that gets dynamically loaded with the names of visible sheets in the workbook. When a user selects one of the names in the dropdown, it activates that sheet. I added VBA code to have the dropdown change the name shown when a user selects a visible sheet using the tabs.

I used two tools to complete the task: The CustomUIEditor from microsoft which I downloaded from here. Do not try to edit a customUI using CustomUIEditor while you have the workbook loaded into excel; if you do, you may lose your work. This is a simple tool for viewing the xml files and creating the callback routines needed by excel. Microsoft needs to put more work into this. I would rather see an editor within the Vsiual Basic editor in Excel.

Andy Pope's RibbonX Visual Designer which can be found here. This is a good tool that is run inside of Excel. It has a preview tool and it shows the built-in icons from MS office. You need to have the workbook unloaded before opening your project.

I just learned over the last week that MS Office 2007 files are nothing more than compressed files containing xml sheets. You can rename a *.xlsm file to a *.zip file and look inside. When you add a custom ribbon to an Excel workbook, it creates a folder in the compressed file called "customUI" and saves a file in that folder called "customUI.xml". My current project is about 40mb large as a .xlsm file. When I unzip it, it becomes over 134mb large. Now I understand why Excel2007 takes so long to load workbooks!

For some reason there are problems showing the xml code in the code window. I will try attach the xml code soon.

Here is the code only related to the dropdown control. Put this into a standard module.


Option Explicit
Public TSNavItems As Integer      'Number of visible sheets (index zero)
Public TSNavArray() As String     'Store names of visible sheets
Public TSNavSelected As String    'Store selected sheet from control
Public TSRibbon As IRibbonUI      'Time Series Custom Ribbon
Public TSWorkbook As Workbook     'Time Series Workbook
Public TrackTSNav As Integer      'Store the current activated sheet index
'This counts and loads the visible sheets and chart sheets into the navigation dropdown
Private Sub LoadVisibleNavigate()
  Dim shtCount As Long, wksSheet As Worksheet, ChtSheet As Chart
  TrackTSNav = 0
  'Count all visible worksheets
  shtCount = 0
  For Each wksSheet In TSWorkbook.Worksheets
    If wksSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
    End If
  Next wksSheet
  'Add the visible Chart Sheets
  For Each ChtSheet In TSWorkbook.Charts
    If ChtSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
    End If
  Next ChtSheet
  'Load all visible worksheets into the Navigation Dropdown array for ribbon
  TSNavItems = shtCount             'index one
  ReDim TSNavArray(TSNavItems - 1)  'index zero
  shtCount = -1
  For Each wksSheet In TSWorkbook.Worksheets
    If wksSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
      TSNavArray(shtCount) = wksSheet.Name
      'Set the default index for the dropdown to the current activated sheet
      If wksSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount
    End If
  Next wksSheet
  'Load the visible Chart Sheets in the dropdown array
  For Each ChtSheet In TSWorkbook.Charts
    If ChtSheet.Visible = xlSheetVisible Then
      shtCount = shtCount + 1
      TSNavArray(shtCount) = ChtSheet.Name
      'Set the default index for the dropdown to the current activated sheet
      If ChtSheet.Name = ActiveSheet.Name Then TrackTSNav = shtCount
    End If
  Next ChtSheet
End Sub
'This gets called when the workbook loads
Private Sub myRibbon_******(ribbon As IRibbonUI)
   Set TSRibbon = ribbon          'My ribbon
   Call LoadVisibleNavigate       'Load the Navigation dropdown
End Sub
'This gets called for every item in the dropdown
'This callback gets called during startup and can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemCount(control As IRibbonControl, ByRef returnedVal)
  'The total number of items in the dropdown box (index one based)
  returnedVal = TSNavItems
End Sub
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemID(control As IRibbonControl, index As Integer, ByRef id)
  'This is an internal id used by xl.  It must be unique
  id = "TSNavSheets" & index
End Sub
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
  'returns the name of the sheet
  returnedVal = TSNavArray(index)
End Sub
'This callback gets called during startup automatically.  It can be called by using
'invalidateControl (ie. TSRibbon.InvalidateControl("drp_Navigate")
Public Sub drp_Navigate_getSelectedItemIndex(control As IRibbonControl, ByRef returnedVal)
  'TrackTSNav is the index of the current selected sheet
  returnedVal = TrackTSNav
End Sub

'This callback only fires when a user changes the dropdown
Public Sub TS_Navigate_OnAction(control As IRibbonControl, id As String, index As Integer)
  TSWorkbook.Activate                 'My workbook
  Sheets(TSNavArray(index)).Activate  'Activate the sheet selected in the dropdown
End Sub

Here is the code that needs to be put into the "ThisWorkbook" module.


Private Sub Workbook_Open()
  xlVersion = Val(Mid(Application.Version, 1, InStr(Application.Version, ".") - 1))
  'Version 12 = Excel2007
  'Version 11 = Excel2003
  'Version 10 = Excel2000
  'Used to decide if the old commandbar menu is loaded
  Set TSWorkbook = ThisWorkbook   'My Workbook
End Sub
'The Navigation dropdown control changes when a user selects a new sheet
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim X As Long
  'Find the index value for the active sheet in the dropdown array
  For X = 0 To TSNavItems - 1
    If TSNavArray(X) = ActiveSheet.Name Then
      TrackTSNav = X
    End If
  Next X
  TSRibbon.InvalidateControl ("drp_Navigate")   'Reset the dropdown control
End Sub


I have a button on my sheet and when the user presses it I want my Label to display the current Date & Time. This will allow the user to
a) know when the sheet was last updated and
b) communicate that the button was pushed and the data was updated successfuly.

I know this is super easy but don't know the exact code, here is what I have included in my command button click subroutine.....


ActiveSheet.Shapes("Label1").Caption = Now

What's incorrect with this? And also, how can I modify the date & time formatting, or where can I learn how to do that?

Thank you so much,

I am trying to create a simple multipage userform with a label on each page
with some text. For the first two pages I can enter in the label by hand.
However, since multipage only comes with two pages, I have to use VBA to add
extra pages. I am able to add the extra pages, but I am unable to add a
label to the new pages. Below is my code. What do I need to do to add a
label to page three?

Private Sub UserForm_Initialize()
MultiPage1.Pages(0).Caption = "Sort by Color"
MultiPage1.Pages(1).Caption = "Summary Report"
MultiPage1.Pages(2).Caption = "New Trouble Time"

End Sub


I've been battleing over this for sometime now. I've created a custom ribbon with a range of buttons and dropdown menus. So far I have been able to get code to run when using OnAction commands for buttons, but I am now trying to use Callbacks to change the enabled state of a dropdown or change the item selected in the dropdown - but this whole business of callbacks and how to use them is really confusing me.

For example I am trying to enable a dropdown called Audience when the sheet called Audience is selected.

This is my code for the Callback


Public Sub AudienceGetEnabled(control As IRibbonControl, ByRef returnedVal)
' Code for getEnabled callback. Ribbon control dropDown

If ActiveSheet.Name = "Audience" Then
    returnedVal = Enabled
End If

End Sub

And this is the code I have when the sheet Audience is selected.


Private Sub Worksheet_Activate()
End Sub

I get an 'Argument not Optional' error when trying to run this Worksheet_Activate code, which from what I've been able to find out online has something to do with the ByRef argument given in the AudienceGetEnabled sub.

I also thought this might work on the Worksheet_Activate code but I get an Object required error


Private Sub Worksheet_Activate()

control.AudienceGetEnabled.returnVal = Enabled

End Sub

Any help would be greatly appreciated.


I have an ActiveX button control (entered from the "Control Toolbox") on a
spreadsheet. I want to programmatically change the caption on the face of the
button. The macro recorder does not work.

Could someone please correct my code (below) or provide other code that
functions? FYI, my code so far:

Dim ctrlX As OLEObject
Set ctrlX = ThisWorkbook.ActiveSheet.OLEObjects("CommandButton2")
ctrlX.Caption = "Click to Enter Date" <<< ERROR HERE!

Thanks much in advance.

This is probably more of an ActiveX question than an Excel question.

I have an ActiveX Label control with a transparent background. When I click on the label the background turns white and stays that way as long as the mouse is over the label, obscuring whatever's underneath. (It also very slightly alters the character spacing.) What object/property controls this? How do I adjust it?

While I'm here, is there an easy programmatic way to assign arbitrary RGB colors to ActiveX controls? The palettes available from the Properties window are fairly limited. A google search for "ActiveX colors" yields a deluge of marketed products, so it's hard to find useful information.

EDITED: I found the answer to my 2nd question: Code:

MySheet.MyControlName.ForeColor = RGB(x,y,z)

I'm still looking for the answer to the transparent background question.

I've seen alot of callback references to iribboncontrols. This basically means that the control has to be interacted with in order for a piece of VBA code to fire. Can you reference an iribboncontrol from a regular module...something where you just have a sub:

the top of my XML code in my UIeditor looks like this:

I've tossed this into a regular module:

Dim moRibbon As IRibbonUI

Sub rxcustomUI_******(ribbon As IRibbonUI)

Set moRibbon = ribbon

End Sub

Sub ChangeToggleButton()

Dim QuickKeysToggleButtons As IRibbonControl

set QuickKeysToggleButtons = ?????????????

End Sub

I'd like to get the toggleButton as an object. Is this possible, or can i only refer to ribbon objects by callbacks???? IF I can only refer to them by callbacks, how can i change their attributes without the user interacting with them???

I'm well aware that excel might not be the best program for something like this, but here goes:

I've got a jpeg of a map, which I'm putting on work sheet one.

Sheets two thru ten focus on individual countries.

I'd like to put 9 hyperlinks on the map, so if I click on the "Croatia" label, it takes me there, or if I click on the "Brazil" label it takes me there, and so on. In short, I want the hyperlinks to take me to pages within the open workbook.

How does one drop a hyperlink on top of an object?

I have an ActiveX Label control on a worksheet; the Caption text is based on cell selection. The source of the Caption text is from a cell indexed in a Named Range on a separate worksheet.

Sometimes the Caption text contains Mathematical Operator Symbols (Unicode(hex) Character Codes 2264 & 2265).

Both of these Unicode Characters initially display as "=" in the Label control. However, clicking on the Label causes the following: The Label slightly increases in size, Character spacing slightly increases, and The Mathematical Operator Symbols are displayed correctly.
As soon as the Mouse Pointer is moved beyond the Label border, the Label reverts to its initially displayed state.

NOTE: The Symbols display correctly in a Label on a UserForm.

Thanks in advance for any assistance.


I have used a variation of the Non-activex Datepicker Calendar Control from the open source forum.

The user clicks the command button to get the calendar to pop up - perfect.

But if it’s an error or they change their mind I need the pop up to disappear if the user clicks anywhere else in the user form.

I had done this through a transparent label the size of the user form.
I set its visible property to false on initialisation.
Activating the calendar pop up brings the label between the rest of the user form and the date picker & sets it to visible.

I then use the click event on the label to simulate a cancel click on the pop-up itself. This then hides both the pop up and the label, allowing the user to continue.

This works beautifully until I try to deploy the solution on a Userform with frames. The label will not show in front of the frames.

Is this "by design" from MS?

I've tried setting the focus to the pop up but as its just a frame and a collection of labels there's no real entry or exit to trigger the events.

I could change the label for a frame as my experiments (on empty userforms /projects) seem to support that this will work but can anyone please suggest a more elegant solution?
up to disappear if the user clicks anywhere else in the user form.

I had done this through a transparent label the size of the user form.
I set its visible property to false on initialisation.
Activating the calander pop up brings the label between the rest of the user form and the date picker & sets it to visible.

I then use the click event on the label to simulate a cancel click on the pop-up itself. This then hides both the pop up and the label, allowing the user to continue.

This works beutifully until I try to deploy the solution on a Userform with frames. The label will not show in front of the frames.

Is this "by design" from MS?

I've tried setting the focus to the pop up but as its just a frame and a collection of labels there's no real entry or exit to trigger the events.

I've tried changing the label for a frame as my experiments (on empty userforms /projects) seem to support that this will work but of course (unless I've missed it) you can't have a transparrent frame.

Can anyone please suggest a more elegant soluiotn?



GotFocus and LostFocus for activex controls...

These two events are straight forward for a commandbutton or a spinbutton , but when does a label or an Image control get or lose focus ?

when using a message box to test these events
msgbox "got focus"
msgbox "lost focus"

Clicking on a label or an image control doesn't give either of these controls focus.

Hi all

I have the following code to click a button on a website.


Public Sub Lehman()

Dim ie As Object

Set ie = CreateObject("internetexplorer.application")

ie.Visible = True

ie.navigate "mywebsite"

While ie.busy


ie.document.all("/LL/dispatcher?show_nav=Y").Click  '<----- Error 91 Object variable or with block var not set

End Sub

The website opens correctly, but I get an error on the line set above. The website is mostly text and has a button to proceed. Here is the web code that references the button:


<h1>Click below to acknowledge
<a href="/LL/dispatcher?show_nav=Y"><img src="/LL_S/imagelibrary/buttons/proceed_orange.gif" alt="proceed" /></a></h1>


A couple questions:
1) Do I need to have any particular references checked to be able to navigate websites in Excel VBA?
2) I can't really read web code yet, what should that line be to click the button to proceed?



Stuck again..

I have a line of code that reads

If (P20) < 1 Then GoTo ????

I want the ???? to refer to the last line of the Sub which is..

Sheets("Customer List").Select

In other words to miss out all other lines of the code and go directly to the last line. I keep getting told to either refer to a line label or a line number, currently the code does not have line numbers (I think) and I dont know how to create a "label" that refers to a line within the code.....


Some of my label controls change their apparent font size when moved to a different Top property on the form during runtime. Others do not.

All the label controls are set in design-time to have a font size of 9. For the problem label controls, even if I explicitly set them to 9 in run-time after moving them, they still display as what appears to be a font size of 10. Even if I change the font size to 8, and then to 9 -- or to 10, then to 9 -- in run-time after moving them, they still display as what appears to be font size 10.

When I check the problem label control's font.size property, it reports the font size it should have (9), not the larger font size that it displays as (10).

I don't see any difference in the label properties. I tried copying one of the label controls that did not have the problem, and changed its name and caption to replace one of the problem label controls, and deleted the original problem label control. But the problem was still there.

I tested in Excel 2010, 2007, 2003, 2002, and 2000, and all had the same problem. I tried 97 too but my project is not compatible with it, so I'm not sure if 97 has the same problem.

For the label controls that are affected by the problem, the problem occurs when I change the label control's Top property during runtime. If I don't change the control's Top property, the problem does not happen for that control.

Any idea why this problem is happening, and what I can do about it?




I have a number of Line Charts that show Sales and Wages related historical data...

For some reason.. some of the Line Charts have got extra (unwanted) Horizontal Category(Axis) Label entries in the "Select Data Source" popup..

These extra (unwanted) Horizontal Category(Axis) Label entries show as being blank but when i press edit they are linked to data..

Basically.. on the attached Workbook.. all Line Charts that are on the 2 History sheets (Catering History and Bar History).. should only show July to June.

The issue is that some have 2 -4 extra Category entries to the right of June.. I just can't seem to get rid of them..

Hopefully someone has encountered this before and can help me..

Hi... i found one code to create a checkbox since i cannot resize the default checkbox in the ActiveX Control... now my problem is i want to link my Label(ActiveX Control) to a cell i.e (A14) so that everytime I click on my Label it calculate my formula in cell A14..thanks..

I have some code built to delete specific rows of data. It starts at the bottom of the document and moves up. It will do the first delete fine but then errors on the next line. Am i missing something between each delete command?


Sub DbDatabase_Conversion()
Dim wks As Worksheet

Set wks = Sheets("April 2010 Daily Board")

wks.Range("173:500").EntireRow.Delete Shift:=xlUp
wks.Range("171").EntireRow.Delete Shift:=xlUp
wks.Range("166:167").Delete Shift:=xlUp
wks.Range("163:164").Delete Shift:=xlUp
wks.Range("161").Delete Shift:=xlUp
wks.Range("136:155").Delete Shift:=xlUp
wks.Range("127:129").Delete Shift:=xlUp
wks.Range("125").Delete Shift:=xlUp
wks.Range("117:119").Delete Shift:=xlUp
wks.Range("115").Delete Shift:=xlUp
wks.Range("54:109").Delete Shift:=xlUp
wks.Range("52").Delete Shift:=xlUp
wks.Range("46:48").Delete Shift:=xlUp
wks.Range("43").Delete Shift:=xlUp
wks.Range("35:37").Delete Shift:=xlUp
wks.Range("32").Delete Shift:=xlUp
wks.Range("25:27").Delete Shift:=xlUp
wks.Range("13:15").Delete Shift:=xlUp
wks.Range("1:8").Delete Shift:=xlUp

End Sub

Hi, I attached my workbook so you guys can easily take a look.
In this workbook I have 2 hidden worksheets.

To start off, you click the Add Week button, put in a date (mm-dd-yy), that brings up the sheet thats named with the date you just put in the text box. (Which happens to be placed in the wrong spot) In that sheet that appears theres a button to "Delete last" which is suposed to delete the last sheet in the worksheet but the new sheet gets placed between the existing charts. Thats my first problem.

Next, when you hit the delete button, my VBA code is set to make one of the hidden sheets visible so it can delete one of the rows.


Sheets("Total WO Table").Visible = True

Its fine deleteing the row, but when I go back and select this sheet again I get an error with the selection.


Worksheets("Total WO Table").Select

even though I have the exact same line of code just before and it doesnt give me an error.

Heres a snipet of the code but you guys can take a look at the actual file for the full thing.


    Sheets("Total WO Table").Visible = True
    Worksheets("Total WO Table").Select
    Sheets("Total WO Table").Move Befo =Sheets(4)
    nameD = ActiveSheet.name                 'Name of last sheet as variable
    ActiveWindow.SelectedSheets.Delete      'Deletes last sheet
    Worksheets("Total WO Table").Select   '<--- This is where error occurs.