Shapes.ComboBox

[Jabberwokki]

Hi there

Newbie posting for the first time so apologies if I don't provide the required detail....

I have the following code written in a module to select a number of macros based on the selection in the Shapes.ComboBox.

Sub TFSStatus_Change()

Dim c As ControlFormat
Set c = Sheet1.Shapes("TFSStatus_ComboBox").ControlFormat

'Choose which macro to run based on the selected value in the combobox

Select Case c.Value
Case 1: ipTimestamp
Case 2: compTimestamp
Case 3: backlogTimestamp
Case 4: blockedTimestamp
Case 5: nrTimestamp
End Select

End Sub

I'm looking to apply a default background and font colour (pre selection) and then a background colour change based on the combobox selection...

Any help would be greatly appreciated.

Regards

D

 

[Fluff]

Hi & welcome to MrExcel.
If you are talking about the background & font colour of a form control combo, then I'm afraid you are out of luck.
To the best of my knowledge there is no way to do that.

 

[Jabberwokki]

Hi & welcome to MrExcel.
If you are talking about the background & font colour of a form control combo, then I'm afraid you are out of luck.
To the best of my knowledge there is no way to do that.

Okey Doky...Thank you for the response. Assume I would need an activeX ComboBx instead then?

Regards

D

 

[Fluff]

Yes you would, although ActiveX controls are notoriously flaky & best avoided.

 

[Jabberwokki]

Ok....So maybe worth asking your advice then....
I'm creating a KanBan board. I'm looking to create a combobox as a status selection tool with entries 'In progress', 'Complete', 'Backlog' etc. I'd like to populate separate cells with timestamps based on when a new status is selected from the combobox and change the background colour of the combobox for visual clarity....i.e green for complete, blue for in progress, red for backlog etc...

What would you recommend?

Regards

D

 

[Fluff]

Why not use a Data Validation drop down. That way you can change the cell format as required.

 

[Jabberwokki]

Why not use a Data Validation drop down. That way you can change the cell format as required.

That's actually how I have the status currently set up with conditional formatting applied to control the background fill based on the item selected. However, the timestamp in different ells then becomes an issue unless I use a circular formula.....

 

[Fluff]

You could use a worksheet change event looking at the cells with DV to add the time stamp.

 

[Jabberwokki]

You could use a worksheet change event looking at the cells with DV to add the time stamp.

Ok cool. Really appreciate the advice....Thank you. I've not come across worksheet change events so I'll do some research and perhaps pick your brains again if needed?

Regards

D

 

[Fluff]

Ok, any problems just shout.