Move sheet code to workbook

[VegasNath]

Hi,

How can I convert the following sheet code to the workbook module?

Code: Select all

Private Sub ComboBox1_Click()
    Me.ComboBox1.Value = Format(Me.ComboBox1.Value, "dd mmmm yyyy")
End Sub

I tried:

Code: Select all

Sub ComboBox1_Click()
    Sheets("Cash Report").ComboBox1.Value = Format(Sheets("Cash Report").ComboBox1.Value, "dd mmmm yyyy")
End Sub

My reasoning for this is that I want to be able to move the sheet to a new book without the code following it.

[HansV]

You can't. The code for an ActiveX control MUST be in the worksheet module.

You might use a Forms control instead, since you can assign it a macro in a standard module. Unfortunately, a Forms combo box works slightly differently than an ActiveX combo box.

[VegasNath]

GRRRRRRRRRRRRRRR.
It was never an issue in 2003, but (in 2007) moving a sheet with code to a new book and then saving the book as .xls produces the following:

Cap1.GIF

Any way to bypass this?

[HansV]

That's because you're trying to save as a .xlsx workbook. You should save as a macro-enabled .xlsm workbook.

BTW, AOL is bouncing e-mails from Eileen's Lounge to you; they say that the block will be lifted within 24 hours.

[VegasNath]

Thanks for the AOL note Hans, it maybe because my mailbox is jam packed, I've deleted some large mail so hopefully should be ok now.

No, I am using 2007 but saving files as old xls files. This is because the files are sent to people still using 2003, who prefer not ro receive 'newer' file versions.

Another (separate) problem is that when I go to open the xls files that have been saved with 2007, I get the following error.

Cap2.GIF

So annoying!

[HansV]

I suspect that you're not saving the workbooks correctly. Can you post the line(s) you use to save them?

[VegasNath]

wb0.SaveAs Filename:=strPath2 & "X " & Format(strDateWork, "yyyy-mm-dd") & ".xls"
wb1.SaveAs Filename:=strPath2 & "Y " & Format(strDateWork, "yyyy-mm-dd") & ".xls"

However, when I open a .xls, update it and then use the following to 'save as', I do not get the warning.

wb2.SaveAs Filename:=strPath2 & "Z " & Format(strDateWork, "yyyy-mm-dd") & ".xls"

[HansV]

Because you don't specify the file type, you're actually saving the workbooks in .xlsx format but with a .xls extension.

Use

wb0.SaveAs Filename:=strPath2 & "X " & Format(strDateWork, "yyyy-mm-dd") & ".xls", FileFormat:=xlExcel8

[VegasNath]

Great, Thanks Hans, I did not realise that at all.

[Jan Karel Pieterse]

To answer your original question: Yes we can :-)

Put this code in the ThisWorkbook module:

Code: Select all

Option Explicit

Private WithEvents mCtl As MSForms.ComboBox

Private Sub mCtl_Change()
    MsgBox "You changed me:" & mCtl.Value
End Sub

Private Sub Workbook_Open()
    Set mCtl = Sheet1.ComboBox1
End Sub