# Excel 2010 get selected combo box value



## Gram123 (Mar 15, 2001)

Searched all over and tried lots of variations, but I'm obviously missing a trick.

I have some VBA in Excel that inserts a combobox and populates it with static values.
The plan is for the user to select a entry from the combobox, and that value to be stored. Then they run a macro to format the document (including the deletion of the combobox and any related cells such as LinkedCell) and save it to a specific location, with the item that the user selected in the combobox forming part of the filename.

So, to insert and populate the combo:



> Sub Macro1()
> '
> ' Macro1 Macro
> '
> ...


... Fine so far.

But say I select *LMN *from the list - how do I then grab that value?
I know that if I entered the above list of values into cells in the document and used them as the input range of the combo, I could just use the INDEX function on the LinkedCell field to get *LMN*.
But how do I INDEX a list entered in VBA?! Or rather, what do I do instead to get that value?

I've tried various things like:


> Range("L1").Value = curCombo.ControlFormat.Value(curCombo.ControlFormat.ListIndex)


and


> Range("L1").Value = myCombo.ControlFormat.List(myCombo.ControlFormat.Value)


but everything results in an error, either a "424 Object Required", or a "450 wrong number of arguments" or "1004 Unable to get List property of DropDown class", etc

I don't _need _the value to be stored in cell L1 (in fact it would be preferable to have neither that nor the index / LinkedCell in K1, as they'll only have to be deleted later) - as long as Excel remembers the value for when the file is saved.

Thanks you..


----------



## OBP (Mar 8, 2005)

Linked Cell K1 holds the value selected, so you can use that.
You can also populate an Excel Range for the combo values or use a dimensioned array to hold them.
You can also assign the selection to a private or public variable for later use.


----------



## Zack Barresse (Jul 25, 2004)

@OBP: The linked cell will only have the index number of the combobox value.

@Gram123: You can get the value with the ControlFormat.List method using the ListIndex property...

```
Dim shpCB As Shape
    Dim sVal As String
    Set shpCB = ActiveSheet.Shapes("myCombo")
    sVal = shpCB.ControlFormat.List(shpCB.ControlFormat.ListIndex)
```
HTH


----------



## OBP (Mar 8, 2005)

Zack, why have an index number?
Why not just use a range, which has no index numbers, the actual selected value is stored in the linked cell.


----------



## Zack Barresse (Jul 25, 2004)

The index is returned to the linked cell range, not the actual value. At least for me it does, with the code supplied from the OP.


----------



## OBP (Mar 8, 2005)

That is want I mean, change the code to use a range.


----------



## Zack Barresse (Jul 25, 2004)

I'm not sure I follow.  With a Shapes combobox you either get the value from the combobox itself or an index number from a linked cell, since those only give numbers. If you had another cell to use the index (linked cell) to look at an input range then that would work too I guess.


----------



## Gram123 (Mar 15, 2001)

Zack, I tried entering your code below mine (in the same Sub) and I get:

Run-time error 1004: Unable to get the List property of DropDown class.


----------



## Zack Barresse (Jul 25, 2004)

I can't really help you because you didn't post any code.  Could be anything. Works for me. Post your code.


----------



## OBP (Mar 8, 2005)

I have used this to create a combo with a list fill range and a linked cell and it works fine.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=288, Top:=165, Width:=97.5, Height:=21). _
Select
ActiveSheet.Shapes("ComboBox1").Select
Selection.ListFillRange = "j2:j4"
Selection.LinkedCell = "l1"


----------



## Zack Barresse (Jul 25, 2004)

But that's a different control type than what the OP is using. You've added an ActiveX control, part of the OLEObjects class, whereas the OP has an object of the Shapes class, a Form control.


----------



## Gram123 (Mar 15, 2001)

Zack Barresse said:


> I can't really help you because you didn't post any code.  Could be anything. Works for me. Post your code.


As it stands...


> Sub Macro1()
> '
> ' Macro1 Macro
> '
> ...


... with the bold red line being the one that produced the Run-time 1004 error. I haven't DIMmed the curCombo control - something I need to do?



OBP said:


> I have used this to create a combo with a list fill range and a linked cell and it works fine.
> 
> ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
> DisplayAsIcon:=False, Left:=288, Top:=165, Width:=97.5, Height:=21). _
> ...


Yeah, that works for me too, as far as creating a combo box. My original code also successfully created the combo, populated it and added the linked cell showing the Index of the selected item. It just errors when it gets to the part where I try to return the value associated with the Index.
As you can see above, my code populates the control without putting data on the worksheet. The idea was to minimise what I added to the worksheet - anything added will only have to be deleted anyway. A combo box, isn't going to overwrite existing data, but if I was adding a source list of values to the worksheet, I'd have to be careful where I placed it, as the amount of data in these Excel sheets may vary wildly. Although I've set K1 as the linked cell in the code above, this was just so I could see it working on screen whilst testing. The linked cell will actually be in something like cell AZ1 in the finalised version.
But as mentioned, if I can avoid even having a linked cell on the sheet, that would be preferable.



Zack Barresse said:


> But that's a different control type than what the OP is using. You've added an ActiveX control, part of the OLEObjects class, whereas the OP has an object of the Shapes class, a Form control.


That'll be why I couldn't right-click to select (and delete) the control in OBPs version.
I'm not sure it matters whether I use a form control or an ActiveX control. In fact, I have no idea why I should use one rather than the other. As long as I can use VBA to create and populate the control, the user can select the required item, and I can then grab that value, then delete the control and apply the selected value as part of the filename, I don't mind which method is used. The user will be using Excel 2010 (as am I).

For info, the other code that will run after the user has selected the value in the combo is as follows:



> Sub ClearFormats()
> 
> Dim ws As Worksheet
> Dim Pic As Object
> ...


----------



## OBP (Mar 8, 2005)

Have you tried using the "curCombo" that you set the combo to in your code? ie.

sVal = curCombo.List(curCombo.ListIndex)

Have you checked what value the shpCB.ControlFormat.List(shpCB.ControlFormat.ListIndex) and sval actually give you?
If the value is OK and it puts it in the L1 or K1 cell perhaps it means that the line of code 
ActiveWorkbook.SaveAs Filename:= _
"\\server\drive\directory\" & "<value user selected in combo>" & " XYZ " & Format(Now, "yyyymmddhhmmss") & ".xls" _
, FileFormat:=xlExcel8
is actually wrong.
Do you have inverted commas around the "value user selected in combo" (presumably sval)?

Personally I would use a userform with the combo on because it does not need deleting, just showing or hiding.

The problem with creating the combo list is that if in the future the users need to add another option they will have to be an Excel VBA programmer to do so. I would have thought a hidden sheet with the values on would have been a better option.


----------



## Gram123 (Mar 15, 2001)

I figured perhaps I might have to set the ListIndex of the combo to something rather than nothing.
So, a performed a search and found that default state of a combo is allegedly ListIndex = -1, and first selected item is 0.
So I tried setting the ListIndex to zero prior to setting the LinkedCell.
I set a msgbox to pop up to display the selected value. It returned nothing.
So I tried setting the ListIndex to 1 and ran the code. The combobox showed the first value, and the linked cell displayed the index (1). Cool.

This allowed Zack's code to work, returning the appropriate value in cell L1 (in the example code, this would be ABC).
This suggests I don't need the linked cell in K1 at all, and can cut that line from the code.

So, my code is now:



> Sub InsertCombo()
> '
> ' Insert Combo Macro
> '
> ...


However... if the combo box selection is changed, the Index in K1 updates (well, it did before I commented it out), but the value in L1 does not.
So the next thing I need is code that updates the sVal each time the user selects a different value in the combo.


----------



## OBP (Mar 8, 2005)

That code should go in the Combo box's Change event procedure.
But the problem with that is it appears to show the old value and not the new one?????


----------



## Gram123 (Mar 15, 2001)

Post overlap!
As you can see above, I've progressed past the 1004 error.

With regard to your other points,
- I'm not running the latter code yet (where it clears formats and sets the filename and such). I tested that seperately and it works fine without the combo box value in the filename, so yes, when it comes to it I should be adding the sVal value in there and I will indeed put it in inverted commas.

- The code is going to be used on tons of Excel files over time, so it's necessary to save it in the user's Peronal.xlsb, so I didn't want additional sheets (hidden or otherwise) saving in there, and the resulting xls files will be automatically processed by one of our systems, so I don't want hidden worksheets in there (indeed, part of the latter code unhides hidden worksheets), or worksheets containing system-irrelevant data (the value list) that could cause hiccups.

I was thinking of adding a userform initially, but unless it was done usign code and then undone (i.e. deleted after the user had selected the value from the combo - surely easier to just delete the combo), it too would have to be saved to Personal.xlsb, which I thought would bloat the filesize.


----------



## Gram123 (Mar 15, 2001)

OBP said:


> That code should go in the Combo box's Change event procedure.
> But the problem with that is it appears to show the old value and not the new one?????


When you say "That code" do you mean Zack's bit? i.e. :



> Dim shpCB As Shape
> Dim sVal As String
> 
> Set shpCB = ActiveSheet.Shapes("myCombo")
> ...


?


----------



## Gram123 (Mar 15, 2001)

I had a thought...
I don't want the insertion of a combo box to occur every time the user opens an Excel file, so I'll have to apply it to a macro button.

So the procedure will have to be:

- User opens Excel file.
- Decides it is one that needs processing, clicks button to run "insert combo" macro.
- Combo box appears with first entry displaying.
- User selects desired value from combo.
- Relevant value is stored (whether in a cell or just held in code).
- If he has selected the wrong value, user can correct it by selecting a different value.
- When he's happy, clicks button to run "clear formats" macro.
- Clear formats macro deletes the combo box.
- Clear formats macro clears the file's formatting etc and saves file to desired directory, adding the last selected combo box value to filename.


----------



## OBP (Mar 8, 2005)

Yes Zack's code.


----------



## Gram123 (Mar 15, 2001)

I moved the code to a seperate module. If I make a selection in the combo and then manually run Zack's code, it works.

How do I set the change event? Is it just by the Sub's name?
It defaulted to DropDown_Change(), and I've tried cmb_Change(), DropDown_Click() and cmb_Click(), but none of these seem to do anything.


----------



## OBP (Mar 8, 2005)

I don't know how you do it with VBA and recording macros when working with combos does not seem to work either.
I added it after the combo was created by using the developer's menu and after right clicking the combo clicked "View Code" on the main menu.


----------



## OBP (Mar 8, 2005)

The code would appear to be something like this
Worksheets("Sheet1").Shapes("Combo Box 1").OnAction = "Macro1"
where macro1 is the VBA code.


----------



## Gram123 (Mar 15, 2001)

That's got it mate, thank you.
I used:



> ActiveSheet.Shapes("myCombo").OnAction = "Combo_Change"


I'm nearly there now...


----------



## Zack Barresse (Jul 25, 2004)

Re errors. I would separate the code for sure. What I posted was an example of how you would go about getting the value from the combobox. You would still want checks in place though. I'd check for a null value assuming you're still using the linked cell. If you're not using the linked cell, still look at the index number, as it sounds like you found. Default (nothing selected) is -1, like most controls.

Most controls, like arrays, (by default) are 0-based. This becomes confusing, not so much because the first index is a 0, but because not all controls play by these rules. The control you're using actually splits the rule right down the middle - it gets the default -1 value for a nullstring, but it's first item is index 1, not 0. This makes it tough as hell to know what is what sometimes in lieu of poor consistency (not all necessarily Microsoft's fault - it's a long story). 

Personally I like linked cells. I like having the data in the cells, it makes me feel good inside.  But also, besides that is the fact that I can add a worksheet change event to when they do change. This way I don't have to worry about all this pesky shape this, control that, index what, none of it except for one spot, one time, when I'm good and ready. Some have called me petty, some have called me lazy. They're both right.


----------



## Gram123 (Mar 15, 2001)

After I relocated your code to its own module, and pointed to it using OnAction as per OBP's advice, I was able to set the ListIndex to 0 in the InserCombo code instead of 1.
This is a good thing, cos it means the combo doesn't display its first value when it is loaded, so I avoid a situation where the user opens an Excel file, assumes because he can see it that the first entry is selected, and tries to process the file, when actually nothing has been selected (L1 is empty). I guess I could write some additional code to say if the user doesn't change the combo selection, then apply the first value ("ABC" in the example code) to L1, but I've adopted what I think is a preferable solution - pop up a msgbox saying "Please select a Consignee before processing file", followed by an Exit Sub.

I did have another issue - when I ran the Clear Formats code on a blank Excel file ("Book1"), the file was 'cleansed', moved and closed as desired. However, when I ran it on an existing Excel file, it was leaving the EXCEL.EXE *.32 process running, so when I opened the next Excel file, the Personal.xlsb failed to load alongside it, ergo the code was unavailable. I solved this by changing a couple of references to ThisWorkbook in the Clear Formats module to ActiveWorkbook, and adding ensuring that everything I'd declared was set to Nothing before the Application.Quit line.

The only circumstance where this stil fails is if the user closes the Excel file midway through the procedure - i.e. if the Insert Combo code has been run, but the user decides to quit instead of processing the file. If this occurs, they see the "do you want to save changes" dialog (which is otherwise circumvented by the Clear Formats code), and if they were to say Yes, they'd also save the file with the combo box on the worksheet!
I guess for tidiness' sake, I need some to add some Before Close code that includes the line of code that deletes the combo box, clears cell L1 if there's a value in it, and sets the declared variables to Nothing.
What's the method for adding Before Close code?

The final thing to sort is applying the value in L1 to the filename and then clearing the cell.
I hadn't got it to work by referring to sVal in the filename, so I used Range("L1") instead. This is great, except it saves the file with the chosen value in cell L1, and I need to delete it!
Can I set the filename, and then delete the value before the SaveAs operation? If not, can I somehow hold the value from L1 in code, empty cell L1, and then apply it to the SaveAs code? Kinda like cutting from the cell and pasting in the filename...

For ref, here's the full code as it stands:



> Sub InsertCombo()
> '
> ' Insert Combo Macro
> '
> ...





> Sub Combo_Change()
> Dim shpCB As Shape
> Dim sVal As String
> 
> ...





> Sub ClearFormats()
> 
> If IsEmpty(Range("L1")) Then MsgBox "Please identify the Consignee before processing this file!"
> Exit Sub
> ...


----------



## OBP (Mar 8, 2005)

If Sval holds the value in L1 and Range("L1") then just replace
Range("L1")
with 
sval 
in the SaveAs statement.


----------



## Gram123 (Mar 15, 2001)

It doesn't work, unfortunately. The code runs, but the Consignee name is omitted from filename.
I added a msgbox to display the sVal at the start of the ClearFormats code, and it brings up a blank box.

So I tried declaring sVal in the ClearFormats code like:



> Sub ClearFormats(sVal As String)


But when this Sub was run, it resulted in an "Argument not optional" error. If I tried stepping through the code in the code window with F8 presses, it did nothing, and if I clicked the Run button, it brought up the Macros dialog, wherein I could select one of the other macros, but not ClearFormats.

So... then I tried adding a line to the end of the Combo_Change code:



> ClearFormats sVal


I had to remove the


> sVal = Nothing


 line from the foot of the ClearFormats code, but then it ran ok.

Well, it didn't exactly - it succesfully added the Consignee name to the filename, but the Combo_Change code was calling the ClearFormats code before I was ready for the latter to be ran (i.e. I'd not have chance to change the combo box selection if I changed my mind).

So! Can I pass the sVal from the Combo_Change code to the ClearFormats code, without immediately calling the latter?


----------



## OBP (Mar 8, 2005)

You should be able to.


----------



## Gram123 (Mar 15, 2001)

Got it...



> Public sVal As String


rather than declaring it within the Combo_Change Sub.

Just the Before Close code to fathom now...

EDIT: Actually, removing the Combo box will have to be done on Before Save, and unloading Excel.exe on Before Close.


----------



## Zack Barresse (Jul 25, 2004)

You can add workbook events in the ThisWorkbook module. When you go there you'll see two comboboxes at the very top of the code pane. From the left (class, titled "(General)") combobox select Workbook. You'll get a Workbook_Open procedure. Then selecting the "(Declarations)" combobox on the right you'll see a list of all the events available for that class. Here are the BeforeSave and BeforeClose events...


```
Private Sub Workbook_BeforeClose(Cancel As Boolean)
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
End Sub
```
In the BeforeClose event, the Cancel variable is False by default, but if set by True it will cancel the close event, mimicking the action of the user pressing the cancel button.

In the BeforeSave event, the SaveAsUI variable will display the SaveAs dialog box or not, and the Cancel variable acts the same way as in the Close event but cancels the Save instead.

HTH


----------



## Gram123 (Mar 15, 2001)

Ok, so I'm trying to unload the combo box in the workbook Before Save, in case the user decides to quit midway through, and saves their file.
I have to save this code to the ThisWorkbook module of the Personal.xlsb, yet I have to reference whichever workbook the user opened. Til now, In the other code, I've been using ActiveWorkbook to identify the file (as opposed to the Personal.xlsb itself). How do I do that here?

I've tried:



> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> 
> On Error Resume Next
> ActiveWorkbook.ActiveSheet.Shapes("myCombo").Delete
> End Sub


and



> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> On Error Resume Next
> ActiveWorkbook.ActiveSheet.Shapes.Range(Array("myCombo")).Delete
> End Sub


And even



> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> ' Delete Consginee combo-box
> 
> Dim wb As Workbook
> ...


Whichever version I use, when I close the file, it asks if I want to save, but when I re-open the saved file, it still has the combo box resident.


----------



## Gram123 (Mar 15, 2001)

If I run the same code outside of the ThisWorkbook module, it successfully deletest the combo...
So how to run it before a save?


----------



## OBP (Mar 8, 2005)

I never, ever use "resume next" for on error, because if you get an error preventing a required action then the resume next ignores. So it is possible that you have an error you don't know about, you may not of course, but it is something you do need to establish.


----------



## Gram123 (Mar 15, 2001)

My mistake, I actually only used the On Error Resume Next line on the last example.

I figured if I was cycling through open workbooks - i.e. 1 example wb and the Personal.xlsb, then it would error on the latter because it doesn't contain a combo. When I tested it, it was indeed erroring, saying the combo was unknown.

I managed to get it to work, using the following:



> Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
> 
> ActiveWorkbook.Worksheets("Sheet1").*Activate*
> ActiveWorkbook.Worksheets("Sheet1").Shapes("myCombo").Delete
> ...


So although it was the ActiveWorkbook, I still had to Activate it (!)


----------



## OBP (Mar 8, 2005)

Great, perhaps you ought to post the full working code so that others can take advantage of all YOUR hard work.


----------



## Gram123 (Mar 15, 2001)

Ugh, it doesn't work after all...

It's not firing when the user's workbook saves, just when Personal.xlsb saves... because it's the ThisWorkbook module of Personal.xlsb, not of the file. The problem is, this code has to be available to hundreds of Excel files, so I can't save it to the ThisWorkbook module of any individual Excel file.

So what I need is a BeforeActiveWorkbookSaves event.....................


----------



## Zack Barresse (Jul 25, 2004)

Why would you be using code in the Personal.xlsb file? That is a hidden file which is always open with the application, but you don't generally save it. In fact you don't generally change it unless you're adding some code to it which you want your code always available to all open workbooks.

Your code should be, if I understand you correctly, in the ThisWorkbook of the file you're working with, so that when it saves, it's save event is triggered, which will not trigger the save event of your Personal file.

Am I following you correctly?

----------

I refreshed before posting and saw your post where you figured out it wasn't supposed to go into Personal.xlsb. There is another option, to setup a class event, which you can use your Personal.xlsb for, which will basically work on all open workbooks. It takes using a class module though. Here are some detailed instructions to doing this. You'll need to follow them one at a time, so please read carefully.

1) Open the VBE
2) Insert a class module in your PERSONAL.XLSB file
3) Select the class module and rename it to *clsEvents* - this is very important
4) Paste the following code into clsEvents module...

```
Public WithEvents clsExcel As Application

Sub Class_Initialize()
    Set clsExcel = Application
End Sub
```
4.1) There is other code you can post there to tie this out, which I will post shortly
5) Go to your ThisWorkbook module in PERSONAL.XLSB
6) Paste the following code into it...

```
Private XL As clsEvents

Private Sub Workbook_Open()
    Set XL = New clsEvents
End Sub
```
That's it, you're all done. Now when you open Excel, thus opening Personal.xlsb file, the open event for that routine will create a new class event (of your clsEvents class) which runs the initialize routine to set the WithEvents variable to the Excel Application object. This allows you to hook into the application events, including application, workbook, worksheet, etc. In this instance we want the Application object and the events hooked to it.

Now any application events you want to hook to need to be defined in the clsEvents class module. Here are some examples...

```
'/// OPEN EVENT
Private Sub clsExcel_WorkbookOpen(ByVal Wb As Workbook)
'
End Sub

'/// BEFORE SAVE EVENT
Private Sub clsExcel_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    MsgBox Wb.Name
End Sub

'/// BEFORE CLOSE EVENT
Private Sub clsExcel_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
'
End Sub
```
As you can see, in the BeforeSave event I just put a simple message box. Once you (after saving your Personal.xlsb file) open Excel (or you can just run the Open routine to initialize the class) these will now be active. So long as you keep that class variable initialized you'll have the events. Doing things like resetting code or using the End keyword will terminate the class.

Is this more what you're looking for?

EDIT: To use this for specific workbooks you would need to set your criteria to the _Wb_ variable.


----------



## Gram123 (Mar 15, 2001)

Ok....
First up, thank you for your continued help! I'd just started looking at Class Modules yesterday afternoon (not something I'd worked with before), but I hadn't got this far.
So, I've followed your instructions, and moved my BeforeSave and BeforeClose code into clsEvents.

1) Should the other code I'm running (Insert Combo module, Combo_Change module, ClearFormats module) also be within the clsEvents Class Module ? Or in another Class Module ? Currently, they're just regular modules saved in Personal.xlsb.

2) My *Personal.xlsb ThisWorkbook *module =



> Private XL As clsEvents
> 
> Private Sub Workbook_Open()
> Set XL = New clsEvents
> End Sub


And my *clsEvents *class module =



> Public WithEvents App As Application
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
> 
> Private Sub Class_Initialize()
> ...


So basically, a BeforeSave to delete the combo and the value in L1, should the user close and save the document before completing the process; and a BeforeClose that unloads variables in case they cause the Excel.exe *32 process to stay active after Excel is closed.

It still doesn't work, though, so there's still something amiss.
I tried substituting Wb.Worksheets("Sheet1") with ActiveWorkbook.Worksheets("Sheet1"), but this made no difference.
So I inserted a MsgBox into the clsExcel_WorkbookBeforeSave event (after activating the workbook), and found that it doesn't appear, suggesting that BeforeSave is not actually firing. Moved the MsgBox to the clsExcel_WorkbookBeforeClose - same result, no msgbox appears.


----------



## Zack Barresse (Jul 25, 2004)

Regarding the code you posted, I would still recommend you try to clarify what the 'Wb' variable points to. Remember, this will fire on _every_ workbook which is open. If you don't specify what the 'Wb' variable should be looking at, it may not turn out well. I'm assuming you really don't want this code firing on every single workbook which you open.

You wouldn't want to substitute 'ActiveWorkbook' for 'Wb' because the pointer may not be correct. 'Wb' is the variable the event is firing on.



> So I inserted a MsgBox into the clsExcel_WorkbookBeforeSave event (after activating the workbook), and found that it doesn't appear, suggesting that BeforeSave is not actually firing.


If this is the case then the class is not initialized properly. Remember, this will ONLY initialize when you open PERSONAL.XLSB file, which opens when you open Excel itself. If you don't close/re-open Excel, you must run the Open procedure in the ThisWorkbook module of your Personal file. Once initialized the events will capture.



> Should the other code I'm running (Insert Combo module, Combo_Change module, ClearFormats module) also be within the clsEvents Class Module ? Or in another Class Module ?


Since the thread has evolved and morphed, is there any way you could give us a succinct description of what should be happening here? I'm afraid I'm a little lost.


----------



## Gram123 (Mar 15, 2001)

Aye, it has evolved somewhat... Ok, the whole procedure currently goes as follows:

1) User receives several emails a day containing Excel attachments. These could be in xls, xlsx or csv format (they could conceivably be xlsm too, but it's unlikely).

2) The user decides that a particular file needs 'processing'. Processing means all formatting needs stripping out, the file must be renamed according to standard naming convention and saved in xls format to a network drive location. This is currently all done manually, so need to automate as much as possible. The drive location is swept periodically and the data from the files are added to our system. 
The intention is for the code to be based in the individual's Personal.xlsb so it is available to all Excel workbooks he might open.

3) The user clicks the Consignee macro button on a custom group on the ribbon. This fires the InsertCombo code, inserting the combo box, populating it and allowing the user to select the appropriate Consignee.

4) The OnAction argument of the combo in the InsertCombo code fires the Combo_Change code. So, when the user makes (or changes) his selection, the Consignee value (not the Index of the value) is temporarily stored so it can be included in the resulting filename.

5) The Combo_Change code, preceeded by *Public sVal as String*, stores the selected value as sVal and in the cell L1.

6a) If the user saves the file (including if they kill Excel and are asked if they want to save changes) anywhere after point 3) above and before point 7) below, the clsExcel_WorkbookBeforeSave code in clsEvents should run, deleting the combo and clearing contents of cell L1. But this isn't happening. When I re-open the saved workbook, the combo and L1 value are there - they've been saved in the doc.

6b) Before the file closes down, the clsExcel_WorkbookBeforeClose in the clsEvents module should run. This should unload the variables to hopefully ensure the EXCEL.EXE process is closed. This is not currently working either.

7) Assuming the user has followed the normal procedure - i.e. he has NOT saved / closed the file as in point 6a) & 6b).
The user clicks the Process macro button. This runs the ClearFormats code. If they click this without first selecting a value from the combo, they receive a msg asking them to select a Consignee first (and OK = Exit Sub).
If there is a Consignee selected, a vbYesNo msgbox asks them to confirm their selected Consignee is correct. If they've made a mistake, they can click "No", which currently clears the value in L1, and sets the combo box ListIndex back to 0. If they click "Yes", the macro:
- unhides hidden worksheets, rows and columns
- deletes blank worksheets
- switches off Freeze Panes
- Clears all cell formatting
- Removes any pictures, charts or shapes
- Resets worksheet names to Sheet1, Sheet2 etc
- Deletes the combo box and clears cell L1
- Saves As xls to network drive, with filename including the sVal value.
- Closes the ActiveWorkbook and ThisWorkbook, unloading variables as it goes, to hopefully avoid leaving an EXCEL.EXE process running in Task Manager - failure to do this means the Personal.xlsb isn't loaded when the next Excel file is opened, so the code is unavailable.

Intended modifcations:
- Fix the issues in Point 6a and 6b).

- As there is now a confirmation msgbox (in point 7 above), I should be able to do away with one of the macro buttons by combining the Combo_Change module with the ClearFormats module. Instead of the msgbox appearing on a button click, it will appear when the combo selection is made.

- Remove the step where it copies the sVal value to cell L1. If the value exists in the sVal variable, it displays in the confirmation MsgBox, and it saves in the filename, what's the point of putting it in a cell, only to delete it shortly after.

I should be ok with the latter two mods, but I'm lost on the first!

I'm not sure how I can "clarify what the Wb variable points to". It needs to point to whichever workbook the combo box has been inserted into. The user should not have more than 1 of these files open and in progress at one time, and if he has any unrelated Excel workbooks open on his PC, these have no business having this combo box inserted!

Regarding the Before Save and Before Close, I was checking my code vs your instructions. I noticed the object at the top said* (General) *for these two:


> Private Sub *clsExcel*_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)


and


> Private Sub *clsExcel*_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)


So I tried changing the object to *App*, so I now have the code in Private sub *App*_WorkbookBeforeSave (/ ...BeforeClose).
Still doesn't work, though................


----------



## Gram123 (Mar 15, 2001)

I've implemented the 2nd and 3rd mods now.
So the only remaining task is to get it to delete the combo if the workbook is saved and closed rather than processed via clicking the (now sole) macro button.

There are 2 possible scenarios in which the combo deletion is necessary on Before Save, and both are fundamentally the same:

- The user has clicked the Consignee macro button, the combo has appeared, but he hasn't yet selected a value, and he intentionally saves the file, or else he quits and when asked if he wants to save changes, says Yes.

- The user has clicked the Consignee macro button, the combo has appeared, he has selected a value, but then when the confirmation msgbox comes up "Do you want to process this as <consignee>?", he clicks No and then saves the file.

In both instances, the file is in the same state (i.e. has the combo visible with nothing selected), so of course, the following is true:


> ActiveSheet.Shapes("myCombo").ControlFormat.ListIndex = 0


----------



## Zack Barresse (Jul 25, 2004)

I'm wondering why we should even be messing around with adding a combobox here. Why not just put it as a control on the ribbon? You said you're using a custom ribbon anyway, right? Why not just create an add-in which does all of this for them? Even if it's just a single button, it might be easier to just launch a userform and do the code from there.

Assuming you want to create an add-in, so you don't have to manually change/update everyone's PERSONAL.XLSB file, it would do the following:

Always be open when Excel is open
Targeted to run on the active workbook (message box confirming this if desired)
Open a userform with a combobox with pre-defined values
User chooses option from combobox, clicks a 'RUN' button
Button will make a copy of the workbook without any formatting and save to specified location *
Ensure newly created file is closed
Close starting workbook (if desired, perhaps a checkbox on the userform to specify this, set by the user)

*Check could be made to see if the file already exists and present the user with the option to overwrite or cancel.

The 'clear formatting' would go through the list of items you specified above. Does that sound about right? If so, I think it should be an add-in. As such, we would need to know the lowest version this will be used in.


----------



## Gram123 (Mar 15, 2001)

Zack Barresse said:


> I'm wondering why we should even be messing around with adding a combobox here. Why not just put it as a control on the ribbon? You said you're using a custom ribbon anyway, right? Why not just create an add-in which does all of this for them? Even if it's just a single button, it might be easier to just launch a userform and do the code from there.


The reasons why I'd not put a control on the ribbon are a) because I didn't know it was possible, when starting this; and b) from what I've seen since, you write it in xml (is that right?), and I feel rather more confident about using VBA than about XML!



> Assuming you want to create an add-in, so you don't have to manually change/update everyone's PERSONAL.XLSB file, it would do the following:
> 
> Always be open when Excel is open
> Targeted to run on the active workbook (message box confirming this if desired)
> ...


I'd be perfectly happy for this to be a ribbon-based drop-down, if it's easy enough to implement.
Is it straightforward enough to achieve? What would be the procedure for managing / amending the combo values? 
I feel like I'm very close with the VBA we've put together so far, and time is limited for this task, so I don't really want to spend another week or more trying to get an add-in version working. But if it could be done quickly, sign me up!

Cheers!


----------



## Gram123 (Mar 15, 2001)

Gram123 said:


> What would be the procedure for managing / amending the combo values?


Thinking about this, it would be cool if the user could also manage the combo values via a 2nd ribbon control.
Say you had a "Manage Consignees" button control, which would maybe open a userform containing the full list of consignees, and the user could add / edit / delete values as required. When finished, maybe he could click a Done button, which would have OnClick code to update the values in the combo box control (called, say, "Apply Consignee").
Then the user would select a value from the Apply Consignee combo and it would follow the same procedure as the ClearFormats code.

Does this sound possible?!
It may be that 2 seperate controls aren't even necessary - maybe the form could allow both the editing of the values, and the selection of the chosen value to apply to the resulting format-cleared xls file...


----------



## Zack Barresse (Jul 25, 2004)

We can do something like that. Good idea.  Gimme a bit, lemme whip something up.


----------



## Gram123 (Mar 15, 2001)

Ok, I've dl'ed the Custom UI Editor, and by looking at online examples, have managed to add a custom group to the end of the Home tab of an Excel file, with 2 icons - an "Amend Consignees" button and a "Select Consignee" combo.

I take it I will need to create a userform and then write a macro to open it, and run that macro using the Amend Consiginees button click. Guess I'll go and investigate userforms next...

For the "Select Consignee" combo, I've populated it by setting the values as the labels of the custom buttons in the combo.
In the example I saw, each button was set to use a different existing macro. In my version, I guess I'll need them all to use a single macro, but to refer to the label of the custom button when defining the filename (?).


----------



## Gram123 (Mar 15, 2001)

Oop. Cross-posting.
I'll be happy to see anything you come up with!!

Cheers Z.


----------



## Zack Barresse (Jul 25, 2004)

Cross posting? Is this in another forum as well? If so, please post the link. One thing we hate doing is duplicating work.

EDIT: Also, if you have a file started, go ahead and post it.


----------



## Gram123 (Mar 15, 2001)

No, just meant you posted whilst I was writing my post.
This is as far as I've got with the Custom UI Editor so far...



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


I don't really need those SmartArtDemote arrows (just the text will suffice), but when I tried removing them, a blank column still exists for them, and a seperator line.


----------



## Zack Barresse (Jul 25, 2004)

Okay, so I have this as an add-in. It works for me in the way you described, but it will need some tweaking by you. Please go over the code carefully. I'll post it to the thread but it's all included in the file.

There is a worksheet in the add-in titled 'Settings'. This is where the consignee list is housed, as well as where we are storing the value of the combobox, which is used in the callbacks.

This is the XML:

```
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="subUpdateList">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="tabConsignee" label="CONSIGNEE">
				<group id="grpMain" label="Consignee Tools">
					<button id="cmbSaveConsignee" 
						label="Save Consignee" 
						size="large" 
						onAction="subSaveConsignee" 
						imageMso="ExportToVCardFile" />
					<comboBox id="cbConsignee" 
						label="List" 
						onChange="subUpdateItem" 
						getItemCount="subGetCount"
						getItemLabel="subGetText"/>
					<button id="cmbUpdateList"
						label="Update List"
						size="normal"
						onAction="subShowAddList"
						imageMso="FilePrepareMenu" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>
```
There are 2 standard modules. One named 'modCallbacks' and the other 'modSave'. These aren't necessarily important, just how I organized it.

In the *modCallbacks* module:

```
Option Explicit

Public rib As IRibbonUI

'Callback for customUI.onLoad
Sub subUpdateList(ribbon As IRibbonUI)
    On Error Resume Next
    Call RibbonInvalidate(ribbon)
    Settings.Range("SelectedConsignee").ClearContents
    On Error GoTo 0
End Sub

Sub SetRibbon()
    Call RibbonInvalidate
End Sub

Sub RibbonInvalidate(Optional ribbon As IRibbonUI)
    If rib Is Nothing Then
        Set rib = ribbon
        rib.Invalidate
    End If
End Sub


'/////////////////////////////////////////////////////////////////////////////////
'/// COMMAND BUTTON
'Callback for cmbSaveConsignee onAction
Sub subSaveConsignee(control As IRibbonControl)
    If Settings.Range("SelectedConsignee").Value = vbNullString Then
        MsgBox "You haven't selected an item yet.", vbCritical, "ERROR!"
        Exit Sub
    End If
    Call SaveConsigneeFile(Settings.Range("SelectedConsignee").Value)
End Sub


'/////////////////////////////////////////////////////////////////////////////////
'/// COMBOBOX
'Callback for cbConsignee getItemCount
Sub subGetCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = Settings.ListObjects("tblConsignee").ListRows.Count
End Sub

'Callback for cbConsignee getItemLabel
Sub subGetText(control As IRibbonControl, index As Integer, ByRef returnedVal)
    returnedVal = Settings.ListObjects("tblConsignee").DataBodyRange(index + 1, 1)
End Sub

'Callback for cbConsignee onChange
Sub subUpdateItem(control As IRibbonControl, text As String)
    On Error Resume Next
    Settings.Range("SelectedConsignee").Value = text
    On Error GoTo 0
End Sub


'/////////////////////////////////////////////////////////////////////////////////
'/// COMBOBOX EDIT BUTTON
'Callback for cmbUpdateList onAction
Sub subShowAddList(control As IRibbonControl)
    frmList.Show
End Sub
```
In the *modSave* module:

```
Option Explicit

Const SavePath                  As String = "C:\Users\Zack\Desktop\"

Sub SaveConsigneeFile(ByVal Consignee As String, Optional MoveOnlyValues As Boolean = True)

    Dim wbSrc                   As Workbook
    Dim wbNew                   As Workbook
    Dim wsSrc                   As Worksheet
    Dim wsNew                   As Worksheet
    Dim oShp                    As Shape
    Dim bOverwrite              As Boolean
    Dim iVisibleState           As Long
    Dim SheetNum                As Long
    Dim SheetMoveCnt            As Long
    Dim ChartObjectCnt          As Long
    Dim SaveName                As String

    'Check if there is an active workbook
    If ActiveWorkbook Is Nothing Then
        MsgBox "You have to have a file open first.", vbCritical, "ERROR!"
        Exit Sub
    End If
    
    'Check if directory exists
    If Dir(SavePath, vbDirectory) = vbNullString Then
        MsgBox "The save path is invalid. Please see your administrator.", vbCritical, "ERROR!"
        Exit Sub
    End If
    
    'Check if the file exists already
    'Set save file name
    SaveName = "NAME " & Consignee & IIf(MoveOnlyValues, ".xlsx", ".xlsb")
    If Dir(SavePath & SaveName, vbNormal) <> vbNullString Then
        If MsgBox("File already exists. Overwrite?", vbExclamation + vbYesNo, "OVERWRITE FILE?") = vbYes Then
            bOverwrite = True
        Else
            Exit Sub
        End If
    End If

    'Ensure user wants to continue
    If MsgBox("Are you sure you want to save this file?", vbYesNo + vbDefaultButton1, "CONTINUE?") <> vbYes Then
        Exit Sub
    End If

    Call TOGGLEEVENTS(False)

    Set wbSrc = ActiveWorkbook
    Set wbNew = Workbooks.Add(xlWBATWorksheet)
    SheetMoveCnt = 0
    For Each wsSrc In wbSrc.Worksheets
        If WorksheetFunction.CountA(wsSrc.Cells) > 0 Then
            'Count of sheets moved
            SheetMoveCnt = SheetMoveCnt + 1
            iVisibleState = wsSrc.Visible
            wsSrc.Visible = xlSheetVisible
            If MoveOnlyValues = True Then
                'Copy sheet contents (no need to remove anything else
                Set wsNew = wbNew.Worksheets.Add(After:=wbNew.Worksheets(wbNew.Worksheets.Count))
                wsSrc.Cells.Copy
                wsNew.Range("A1").PasteSpecial xlPasteValues
            Else
                'Copy sheet
                wsSrc.Copy After:=wbNew.Worksheets(wbSrc.Worksheets.Count)
                Set wsNew = wbNew.Worksheets(wbNew.Worksheets.Count)
                'Remove objects (pictures, shapes, etc)
                On Error Resume Next
                For Each oShp In wsNew.Shapes
                    oShp.Delete
                Next oShp
                On Error GoTo 0
                'Clear formats
                wsNew.Cells.ClearFormats
                wsNew.Cells.EntireRow.Hidden = False
                wsNew.Cells.EntireColumn.Hidden = False
            End If
            wsSrc.Visible = iVisibleState
        End If
    Next wsSrc

    'Check if any sheets moved
    If SheetMoveCnt = 0 Then
        wbNew.Close False
        MsgBox "There was no data to move.", vbCritical, "ERROR!"
        GoTo ExitRoutine
    End If

    'Remove first (blank) sheet, rename worksheets & remove freeze panes
    If MoveOnlyValues = False Then wbNew.Activate
    wbNew.Sheets(1).Delete
    SheetNum = 1
    For Each wsSrc In wbNew.Worksheets
        wsSrc.Name = "||TEMP_NAME||" & SheetNum
    Next wsSrc
    SheetNum = 1
    For Each wsSrc In wbNew.Worksheets
        wsSrc.Name = "Sheet" & SheetNum
        If MoveOnlyValues = False Then
            wsSrc.Activate
            ActiveWindow.FreezePanes = False
        End If
    Next wsSrc

    'Save file, delete if it was there previously
    If bOverwrite = True Then Kill SavePath & SaveName
    wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, 51, 50)
    wbNew.Close
    
    MsgBox "File saved successfully!", vbInformation, "COMPLETE!"

ExitRoutine:
    Call TOGGLEEVENTS(True)

End Sub

Sub TOGGLEEVENTS(blnState As Boolean)
    'Originally written by Zack Barresse
    Application.DisplayAlerts = blnState
    Application.EnableEvents = blnState
    Application.ScreenUpdating = blnState
    If blnState Then Application.CutCopyMode = False
    If blnState Then Application.StatusBar = False
End Sub
```
There is a single userform. The following controls are in it:

Listbox - named 'lbList'
Commandbutton - named 'cmbAdd'
Commandbutton - named 'cmbRemove'

The userform is titled 'frmList', with the following code...

```
Option Explicit

Private Sub UserForm_Initialize()
    Me.lbList.List = Settings.ListObjects("tblConsignee").DataBodyRange.Value
End Sub

Private Sub cmbAdd_Click()
    Dim sAdd                    As String
    Dim iRow                    As Long
    sAdd = InputBox("Add to list:")
    If Trim(sAdd) <> vbNullString Then
        sAdd = Trim(sAdd)
        If Settings.ListObjects("tblConsignee").DataBodyRange Is Nothing Then
            Settings.ListObjects("tblConsignee").ListRows.Add 1
        Else
            Settings.ListObjects("tblConsignee").ListRows.Add Settings.ListObjects("tblConsignee").ListRows.Count + 1
        End If
        iRow = Settings.ListObjects("tblConsignee").ListRows.Count
        Settings.ListObjects("tblConsignee").DataBodyRange(iRow, 1).Value = sAdd
        MsgBox "'" & sAdd & "' added successfully.", vbExclamation, "SUCCESS!"
        Unload Me
        Call SetRibbon: rib.Invalidate
    End If
End Sub


Private Sub cmbRemove_Click()
    Dim iFound                  As Long
    Dim iStep                   As Long

    iFound = -1
    For iStep = 0 To Me.lbList.ListCount - 1
        If Me.lbList.Selected(iStep) = True Then
            iFound = iStep
            Exit For
        End If
    Next iStep
    If iFound <> -1 Then
        If MsgBox("Are you sure you want to remove '" & Me.lbList.List(iFound) & "'?", vbYesNo + vbDefaultButton2, "REMOVE ITEM?") = vbYes Then
            Settings.ListObjects("tblConsignee").ListRows(iFound + 1).Delete
            If Settings.ListObjects("tblConsignee").DataBodyRange Is Nothing Then
                Settings.ListObjects("tblConsignee").ListRows.Add 1
            End If
            Me.lbList.Clear
            Me.lbList.List = Settings.ListObjects("tblConsignee").DataBodyRange.Value
            Call SetRibbon: rib.Invalidate
            MsgBox "'" & Me.lbList.List(iFound) & "' removed successfully.", vbExclamation, "SUCCESS!"
        End If
    Else
        MsgBox "You must select something first.", vbCritical, "ERROR!"
    End If
End Sub
```
This should be basically everything you need.

PLEASE NOTE I set the save path locally to my desktop, it will need adjusting, as well as the save name. I'm not sure what you wanted for that. Right now it saves (by default) in xlsx format. If you specify the second variable of the save routine to be False, then it will save it as xlsb format. This is in case there are macros. If we just move the values, we know there aren't any. For more info on file types look here:

http://www.rondebruin.nl/win/s5/win001.htm

Let me know if this works for you.


----------



## Gram123 (Mar 15, 2001)

Looks awesome, cheers Z!
Just tweaking now...


----------



## Gram123 (Mar 15, 2001)

Ok...

1) Dumb question, but how do I apply this add-in so it's available whenever I open Excel?
2) How do I edit the hidden worksheet containing the combo values (tblConsignee)?
3) Is it possible to re-sort (alphabetise) the worksheet (or at least, the combo) whenever a new value is added by the user?

4) I open the xlam file and then go to File and open an existing Excel file. When I try to process the file (Save Consignee), I get a Run-time error '1004' - cannot rename a sheet to the same name as another sheet, a referenced object library or a workbook referenced by Visual Basic."
The DEBUG is on the modSave module, on the line:


> wsSrc.Name = "||TEMP_NAME||" & SheetNum


If I click END, the formatting is stripped, but the file is obviously not saved. I think this occurred because there were 2 worksheets in the test doc.

5) When I try to remove an item, after clicking Are You Sure = YES, I get "Run-time error '381' - Could not get the List property. Invalid property array index."
The DEBUG is on the frmList, cmbRemove on the line


> MsgBox "'" & Me.lbList.List(iFound) & "' removed successfully.", vbExclamation, "SUCCESS!"


This did work at first, but after adding and removing a few items, it seems to be having trouble...

6) The resulting files must be in *xls *format and must not contain any macros - i.e. if an .xlsm is received, for example, containing a macro, that needs to be stripped out.
My previous save operation was:



> ActiveWorkbook.SaveAs Filename:= _
> "C:\Users\<my login>\Desktop\XYZdirectory\" & Format(Now, "yyyymmddhhmmss") & " XYZ" & sVal & ".xls" _
> , FileFormat:=xlExcel8


In your code, I've change the path...


> Const SavePath As String = "C:\Users\<my login>\Desktop\XYZ directory\"


I guess I'll need to change the *check *for existing file from:


> SaveName = "NAME " & Consignee & IIf(MoveOnlyValues, ".xlsx", ".xlsb")


to:


> SaveName = Format(Now, "yyyymmddhhmmss") & " XYZ" & Consignee & IIf(MoveOnlyValues, ".csv", ".xls", ".xlsx", ".xlsm", ".xlsb")


as the files received could potentially be in any of these formats.

And I'll need to change the save operation at the end, from:


> If bOverwrite = True Then Kill SavePath & SaveName
> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, 51, 50)
> wbNew.Close


to:


> If bOverwrite = True Then Kill SavePath & SaveName
> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, *56*)
> wbNew.Close


to ensure they're saved as .xls.

Does that look right?


----------



## Gram123 (Mar 15, 2001)

Gram123 said:


> Ok...
> 
> 1) Dumb question, but how do I apply this add-in so it's available whenever I open Excel?


Solved this one!!...


----------



## Gram123 (Mar 15, 2001)

In trying to get around this one:


Gram123 said:


> 2) How do I edit the hidden worksheet containing the combo values (tblConsignee)?


I thought I'd just edit the consignees via the ribbon button - delete the fake values (ABC, DEF and so on) and insert the proper values.
When I deleted values from the top of the list, they disappeared without problem. When I deleted the values from the bottom, I was seeing this error:


> 5) When I try to remove an item, after clicking Are You Sure = YES, I get "Run-time error '381' - Could not get the List property. Invalid property array index."
> The DEBUG is on the frmList, cmbRemove on the line


I continued and now am getting another error: "Run-time error '91': Object variable or With block variable not set". Debug on the line:


> rib.Invalidate


in the Sub RibbonInvalidate(Optional ribbon As IRibbonUI).

Then, on closing the Excel file and re-opening, I find that all of the deleted values return! Even the ones that hadn't errored when deleted!
So.... I need the amended list to be saved after each addition or deletion.

I also found when deleting values that the confirmation was telling me it had deleted the subsequent entry to the one I'd actually removed!
e.g.
- I clicked on *ABC *and clicked Remove
- msg "Are you sure you want to remove *ABC*?"
- I click Yes
- msg "*DEF *removed successfully"

I think some of these issues (error 91, and maybe also it reporting that it has removed the wrong entry) might have been caused by a 'bad' value in the list. I initially added 456 to the list in testing, and then tried to delete it, resulting in error 381. I think maybe because I ignored this error and carried on (I probably saved the xlam), it hasn't deleted properly, and is getting confused.


----------



## Gram123 (Mar 15, 2001)

Any ideas on the above?

Regarding the processing of the file, when I said...


> :
> I guess I'll need to change the check for existing file from:
> 
> SaveName = "NAME " & Consignee & IIf(MoveOnlyValues, ".xlsx", ".xlsb")
> ...


I realise my error now. First up, it's an IIF, so I should only have 2 options there - (if xyz is true, then 1 else 2), not 5 different filetypes; and secondly, I see that this is for the save operation, not the filetypes that the module is able to process, so it only needs to save as *.xls.
So... I've changed the line to:


> SaveName = Format(Now, "yyyymmddhhmmss") & " XYZ " & Consignee & IIf(MoveOnlyValues, ".xls", ".xlsb")


This now works as desired, as long as there is only 1 worksheet in the doc being processed. There shouldn't be any situation where I need to save as xlsb. In fact, if it did, the file would not process into our system. If we received an email attachment that had macros saved in it, I'd need to strip them out and still save the file as *.xls.


----------



## Gram123 (Mar 15, 2001)

Erk... no it doesn't work as desired.
When I open the xls file, it says:



> The file you are trying to open, '20130924095119 XYZ ABC.xls', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?


So I'll need to ensure the file is saving as a true xls.


----------



## Gram123 (Mar 15, 2001)

Ok, got it to save as proper xls and open, by changing the save filetype to:



> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, *56*, 50)


Also, by replacing the SheetNum variable with wsSrc.index, it stopped erroring when saving multi-sheet documents:



> For Each wsSrc In wbNew.Worksheets
> wsSrc.Name = "||TEMP_NAME||" & *wsSrc.index*
> Next wsSrc
> 
> ...


However, I now find an interesting new problem - the workbook it produces does indeed have 2 sheets, correctly named, but both of them contain the data from Sheet2 of the source doc (or whatever the 2nd sheet has been given)...

Things are getting confusing, and I think my many posts and attempted fixes are not making it any simpler.
Ok, what I'll do is, tomorrow, I'll post the full code and a full list of outstanding issues, and hopefully that will clarify what's going wrong.


----------



## Gram123 (Mar 15, 2001)

Ok, here's the full code:

*frmList code*



> Option Explicit
> 
> Private Sub UserForm_Initialize()
> Me.lbList.List = Settings.ListObjects("tblConsignee").DataBodyRange.Value
> ...


*modCallbacks
*


> Option Explicit
> 
> Public rib As IRibbonUI
> 
> ...


*modSave
*


> Option Explicit
> 
> Const SavePath As String = "C:\Users\me\Desktop\XYZ directory\"
> 
> ...


----------



## Gram123 (Mar 15, 2001)

And the XLAM file...



> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="subUpdateList">
> <ribbon startFromScratch="false">
> <tabs>
> <tab idMso="TabHome" >
> ...


----------



## Gram123 (Mar 15, 2001)

And the outstanding problems...

1) Is there a way to do edit the hidden worksheet containing the combo values (tblConsignee)?

2) Is it possible to re-sort (alphabetise) the worksheet (or at least, the combo) whenever a new value is added by the user?

3) If I select a Consignee from the ribbon combo and click Process XYZ, the confirmation msgbox comes up as expected. "No" cancels as expected. "Yes" saves an xls file in the correct dir with the desired filename. When the file is opened, the formatting has been stripped, as required, and the sheets have been renamed as required. However....

a) Sometimes when I open a file, a message appears:

'The file you are trying to open, '20130926152913 XYZ STU.xls', is in a different format than specific by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?".

I need to stop this message appearing - the file needs to be saved as a true xls file, and I thought I'd resovled this by setting the filetype to "56" in the modSave code:


> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, 56, 50)


But when opening another xlsx file and trying to run the process the file, the msg appeared again.

b) If the file I'm processing has 2 worksheets containing data, the resulting file has 2 worksheets called Sheet1 and Sheet2 as desired. However, both contain the data from the 2nd sheet of the source doc.

4) If I click the Edit Consignees button, select a Consignee from the bottom of the list (say, VWX) and click Remove, I receive the msg "Are you sure you want to remove VWX?", as required. If I click Yes, I receive...

a) "Run-time error '91': Object variable or With block variable not set". Clicking Debug shows the following line of the modCallbacks module as the culprit:



> Sub RibbonInvalidate(Optional ribbon As IRibbonUI)
> If rib Is Nothing Then
> Set rib = ribbon
> * rib.Invalidate*
> ...


I am also now receiving this when adding a new Consignee.

OR

b) I receive "Run-time error '381': Could not get the List property. Invalid property array index". The debug line in this instance is on the frmList code, cmbRemove_Click() on the line:



> MsgBox "'" & Me.lbList.List(iFound) & "' removed successfully.", vbExclamation, "Consignee deleted!"


Sometimes it seems to successfully delete values from the list (despite debug errors) and other times it doesn't (i.e. when opening the next file and checking the combo, sometimes the recently deleted values have returned!). I need the user to be able to permanently delete values from the list, and obviously need the errors to cease!

c) When freshly opening an Excel file and trying to remove the first value from the list...

- Click on ABC and click Remove
- msg "Are you sure you want to remove ABC?"
- Click "Yes"
- msg "*DEF* removed successfully"
- list shows ABC has been removed, and DEF is still there

.... that's it for now...


----------



## Gram123 (Mar 15, 2001)

Ok... by reverting the SaveAs FileFormat to the original non-ribbon method, I no longer receive the warning in point 3a) above.

So, on the modSave module > SaveConsigneeFile, I've dropped the IIF on this line:



> SaveName = Format(Now, "yyyymmddhhmmss") & " XYZ " & Consignee & IIf(MoveOnlyValues, ".xls", ".xlsb")


so it now reads:


> SaveName = Format(Now, "yyyymmddhhmmss") & " XYZ " & Consignee & ".xls"


and changed this line:


> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=IIf(MoveOnlyValues, 56, 50)


to:


> wbNew.SaveAs Filename:=SavePath & SaveName, FileFormat:=xlExcel8


----------



## Gram123 (Mar 15, 2001)

This has now become pretty urgent, so any help Zack or anyone can provide would be very much appreciated.


----------



## Zack Barresse (Jul 25, 2004)

Hey, I've been fairly busy with work, but I'll put this on the top of my list when I have time. Should be tonight or tomorrow.


----------



## Gram123 (Mar 15, 2001)

Thanks Zack, appreciated..


----------



## Gram123 (Mar 15, 2001)

Ok, time constraints mean I'm forced shelve the ribbon method, and instead revert to the original plan of loading the combo onto the workbook and populating it, the user selecting their desired Consginee, and then processing the file.
Basically, I'm back to the situation I was at in posts #38 - #41 of this thread.

In short, before saving (or quitting & saving) the Excel file, I need to check if the combo box "myCombo" is still resident on the worksheet it was created on, and if it is, to delete it. It seems like this should be such a simple step - it was easy enough to add the damn thing, but I just can't seem to get rid of it.
I followed Zack's instructions and have a Class Module containing BeforeSave and BeforeClose subs, but they don't seem to be firing on the workbook, because it fails to delete the combo, and indeed the BeforeSave will also fail to display a msgbox if I add one.
So something's amiss, and with no prior experience of class modules, I don't know what to do next.

Any input gratefully received.


----------

