DROPDown Menus: Can you change the font (ant to bumblebee) ?

[missbluecashew]

Hi,

VBA/Macro below: it provides a detailed dropdown menu that is very difficult to read
and as you can see has a lot of information. However, it returns a
much shorter answer (icon) based on the selection.

I found a macro to change font size that mentions 'index',
but it didn't work and I'm not sure if I'm inserting it correctly anyway.

Most internet info was negative; I do not want to use a Macro that will increase screen size.

Can it be done? Any ideas how?
Thank you for any help/information!

Cathy

Private Sub Worksheet_Change(ByVal Target As Range)



Dim Column As Long
Column = ActiveCell.Column


If Column = 16 Then


'    If Target.Count > 1 Then Exit Sub
'    If Not Intersect(Target, Range("p9:p245")) Is Nothing Then
'    If IsEmpty(Target.Value) Then Exit Sub
'    Application.EnableEvents = False
    
    
    Select Case Target.Value
    Case "1.0  In Budget, Procurement Approved: High priority, non-controversial": Target.Value = "1.0  In Budget, Approved"
    Case "0.9  In Budget, Procurement Awaiting Approval: Fully expected to be approved in near term, 100% support from customers and users, no outstanding issues": Target.Value = "0.9  In Budget, Approval Pending"
    Case "0.8  In Budget, Not Approved, Final Requirements Defined:  High level Champions and nearly 100% support from customers and users": Target.Value = "0.8  In Budget, Not Approved, Final Requirements Defined"
    Case "0.7  In Budget, Not Approved:  Broad requirements defined. RFPs and RFQs issued to prospective vendors": Target.Value = "0.7  In Budget, Not Approved, Broad Requirements Defined"
    Case "0.5  In Budget, Requirement Unclear: RFI possibly received, mixed opinions amongst customer/users communities": Target.Value = "0.5  In Budget, Requirement Unclear"
    Case "0.3  Unbudgeted, in Customer Plans: Good probability of becoming an approved program": Target.Value = "0.3  Unbudgeted"
    Case "0.2  No Plans, Gaining Support: One of several programs in consideration": Target.Value = "0.2  No Plans, Gaining Support"
    Case "0.1  Champion, Lacks Broad Support: Unlikely to become a program in its current form": Target.Value = "0.1  Champion, Lacks Broad Support"
    End Select
    
ElseIf Column = 17 Then


    'If Target.Count > 1 Then Exit Sub
    'If Not Intersect(Target, Range("q9:q245")) Is Nothing Then
    'If IsEmpty(Target.Value) Then Exit Sub
'    Application.EnableEvents = False
    Select Case Target.Value
    Case "Business as Usual": Target.Value = "BAU"
    End Select
    Application.EnableEvents = True
'    End If
    
End If

 

[Smitty]

Your code (as posted) is only evaluating a range to see what value has been selected. It's not actually creating the list.

But if you're using a Data Validation list, then you can use a Combo Box instead, which will allow you to modify the Font and Size.

http://www.contextures.com/xlDataVal10.html

 

[missbluecashew]

ok, thanks! Checking it out now.
I saw something about combo boxes, but wasn't sure...
haven't used Excel (except in school) for many years -- no exp in VBA.

Have a great day!

 

[Smitty]

I saw something about combo boxes, but wasn't sure...
haven't used Excel (except in school) for many years -- no exp in VBA.

Not to worry, Debra's posted the complete code there so you shouldn't have to do too much. I believe she also has a copy you can download if needed. But if you get stuck just post back here and let us know where.