ummm... strange Excel VBA routine

[hopelessliar]

I have been provided with an excel sheet that allows me to upload to a database. It's very useful and I thought I could adapt it to upload other pieces of information. So I took a look at the code that was doing this and I cannot fathom it at all. Can anyone give me any pointers here?

2 scripts:


Private Sub Workbook_Open()

Dim cbrStandard As CommandBar
Dim mnuNewMenu As CommandBarControl
Dim cmdNewCtrl As CommandBarButton

Set cbrStandard = Application.CommandBars.ActiveMenuBar

Set mnuNewMenu = cbrStandard.FindControl(, , "DDS&quot
If Not mnuNewMenu Is Nothing Then Exit Sub

Set mnuNewMenu = cbrStandard.Controls.Add(Type:=msoControlPopup, Temporary:=True)
mnuNewMenu.Caption = "DDS O&ptions"
mnuNewMenu.Tag = "DDS"

Set cmdNewCtrl = mnuNewMenu.Controls.Add(Type:=msoControlButton, Id:=1)

cmdNewCtrl.Caption = "&Upload CPT Scheme"
cmdNewCtrl.TooltipText = "Upload CPT Scheme to DDS Server"
cmdNewCtrl.Style = msoButtonCaption
cmdNewCtrl.OnAction = "DDSCPTUpload"


End Sub

and then this module:

Public Sub DDSCPTUpload()
'
' DDSCPTUpload Macro for Excel 97
'
Dim objDDSCPTUpload As Object

Set objDDSCPTUpload = CreateObject("DDSCPTUpload.clsCPTUploadUI&quot
objDDSCPTUpload.Start Application
Set objDDSCPTUpload = Nothing

End Sub

I'm no VBA expert but I can usually follow what's happening enough to make modifications, this has me mystified.

 

[Loomah]

Hi
This is the third time I've suggested this in as many days but you'd be better off posting this on the VBA forum!! Many people subscribe to both but not all VBA experts visit this forum.

But Your starter for ten is
The first routine appears to look to see if a menu comand exists in the workbook and then if it doesn't it creates it. The function of the command button once created is to enable the user to run your second routine. As this isn't straight forward for me to explain I've tried to comment the code below with what I think is happening...

Private Sub Workbook_Open()
    Dim cbrStandard As CommandBar
    Dim mnuNewMenu As CommandBarControl
    Dim cmdNewCtrl As CommandBarButton
    
    'create an object referring to the workbook menu toolbar
    Set cbrStandard = Application.CommandBars.ActiveMenuBar
    
    ' see if the "DDS" menu exists on the menu bar
    Set mnuNewMenu = cbrStandard.FindControl(, , "DDS")
    'if it does exit the sub
    If Not mnuNewMenu Is Nothing Then Exit Sub
    
    'if it doesn't exist, create it
    Set mnuNewMenu = cbrStandard.Controls.Add(Type:=msoControlPopup, Temporary:=True)
    'give it a caption (what you see)
    mnuNewMenu.Caption = "DDS O&ptions"
    ' and a tag (dunno too much about this!)
    mnuNewMenu.Tag = "DDS"
    
    'create a control in your new menu
    Set cmdNewCtrl = mnuNewMenu.Controls.Add(Type:=msoControlButton, ID:=1)
    'add all the bits you actually see
    cmdNewCtrl.Caption = "&Upload CPT Scheme"
    cmdNewCtrl.TooltipText = "Upload CPT Scheme to DDS Server"
    cmdNewCtrl.Style = msoButtonCaption
    'tell the app what to do if the command is clicked
    cmdNewCtrl.OnAction = "DDSCPTUpload"
End Sub

Sorry but I can't shed any reasonable light on the actual upload proc as it's not something I have extensive experience of.

Happy Friday
;-) If a man says something and there are no women there to hear him, is he still wrong?