using a variable for combo box name?

[MadTom]

I'm tring to use a variable for a combo box name in this statement, Combo1.BackColor = RGB(255, 0, 0) (this works great)


Would like to use CntrlName = Combo1, dim CntrlName as String.

CntrlName,

BackColor = RGB(255, 0, 0) (doesn't work)

Could also use

Screen.ActiveForm.ActiveControl or what ever I need for ActiveControl for each ComboBox.

Would like to use the same code for different comboBox's

I hope I'm clear on what I need, thanks for any help!

[ranman256]

SetBC cboStates
or
SetBC cboCity

sub SetBC(cboBox as combobox)
   cboBox.BackColor = RGB(255, 0, 0)
end sub

[Micron]

I venture to say it's not often one needs to pass a variable instead of a control name or the control itself. If there is a reason you can't do either of these, then try something along the likes of
Forms!yourFormName.Controls(strName).BackColor = vbRed

[moke123]

What is the purpose of the procedure? are you trying to do form validation and highlight missing Fields?
There may be other ways to achieve your result.

[MadTom]

I want to learn more and wanted to make this Sub a Function. My form (frmColorCode) has 5 combo boxes (comb01 to combo5) that will change color when you click on drop down menu. Will add Band value depending on color and placement of Band by clicking on combo box. All works great when I use a Sub for each Band, but I wanted to remove redundancy and make it a Function. The only thing that I haven't got working is adding a variable for combo# when I click on combo# event in my Sub. Combo1.BackColor = RGB is the statement I need to change in my Function. I know there maybe other ways to do this, I'm willing to change. I think the problem is Combo# is a Control not a String or Number?

     Option Explicit
Public Band1 As Integer
Public Band2 As Integer
Public Band3 As Long
Public Band4 As Long
Public Band5 As Long
Public Band3x As String
Public Band3xx As String
Public BandNum As Integer
Public CntrlName As String
Public Function ResistorBands() As String
    'MsgBox CntrlName 'for debuging
Select Case CntrlName 'Variable for Combo Box on frmColorCode
    Case Is = "Black"
        Combo1.BackColor = RGB(0, 0, 0)
        Combo1.ForeColor = RGB(245, 245, 245)
        ResistorBands = 0 ' return Band value
        
    Case Is = "Brown"
        Combo1.BackColor = RGB(156, 102, 31)
        Combo1.ForeColor = RGB(245, 245, 245)
        ResistorBands = 1
    
    Case Is = "Red"
        'Forms!frmColorCode.Controls(Combo1).BackColor = vbRed
        Combo1.BackColor = vbRed
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 2
    
    Case Is = "Orange"
        Combo1.BackColor = RGB(255, 165, 0)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 3
    
    Case Is = "Yellow"
        Combo1.BackColor = RGB(255, 255, 0)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 4
    
    Case Is = "Green"
        Combo1.BackColor = RGB(0, 205, 0)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 5
    
    
    Case Is = "Blue"
        Combo1.BackColor = RGB(92, 172, 238)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 6
    
    Case Is = "Violet"
        Combo1.BackColor = RGB(238, 58, 140)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 7
    
    Case Is = "Gray"
        Combo1.BackColor = RGB(170, 170, 170)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 8
    
    Case Is = "White"
        Combo1.BackColor = RGB(245, 245, 245)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 9
    
    Case Is = "Gold"
        Combo1.BackColor = RGB(255, 215, 0)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 10
        
    Case Is = "Silver"
        Combo1.BackColor = RGB(192, 192, 192)
        Combo1.ForeColor = RGB(0, 0, 0)
        ResistorBands = 20
    Case Else
End Select
    'MsgBox CntrlName & ResistorBands
End Function

Public Sub Combo1_Click()
    CntrlName = Combo1
ResistorBands
    Band1 = ResistorBands
End Sub

Public Sub Combo2_Click()
    CntrlName = Combo2
ResistorBands
    Band2 = ResistorBands
End Sub

[Micron]

Not making sense to me. You will add band value to what when the combo is clicked? Maybe explain how it's supposed to be used as opposed to what your coding attempts are about. Probably you should be using an event pertinent to each combo rather than a separate procedure. While we're at it, let's be sure you understand the difference between a sub and a function. Both are procedures, but a sub cannot return a value to the calling event or procedure. Only a function can do so. Also, only functions can be called from queries and menus or be designated in the property sheet as the procedure to be run by a control event. Since you don't appear to have any of these requirements, why should this be a function? If it's because you couldn't get it to work one way or the other, that's not necessarily going to fix it. It would have helped to see the name of your posted procedure as well.
EDIT - and to know where this resides. The public declaration is interesting as is the lack of a form reference in most cases (except for red)It's beginning to look like the combo should contain at least a column for colour and one for bands so as to eliminate all/most of this code. Thus being more clear about what the user is doing would help.

[MadTom]

Sorry I'm not clear! Band values would be displayed in a lable Box when I click Calc box. What this form does is a resistor Color Code Calculator, you click on the each Band and select a color, each color has a value, and will change the color after clicked of the combo box. Right now working on 4 band calculator, but will also add 5 bands to the form with a check box. I did design this with event click on each combo box, but wanted to cleanup my code and use a Function. My Function does return a value ResistorBands, the value of each band. Maybe an easier way, only learning as I go. Work in progress, will add more to this form as I go. Thanks for your reply, hope this helps.

[davegri]

Bands-davegri-v01.zip
Have a look at this. Lets you use a variable name for the comboboxes...

[Micron]

OK, so how many controls will you end up with? I think I see an advantage to passing the name of the control to a function to set the colour of something but not sure where it makes sense to "draw the line" and say that a standard click event for each combo would be far less efficient. Also, as you seem to be saying, you're wanting to expand your vba experience and knowledge. To that end, I've already given you the answer. However, I could probably make it more concise if you care to hang on a bit.

[accesstos]

My suggestion in the attachment is based on Access's logic.
It is a mini project that contains the basic elements of the proposed approach and I think it is good enough to start.

Good luck!

[Micron]

@accesstos - your db textbox shows #Name error and the combo doesn't change anything for me. Perhaps I am not using it correctly?

Anyway, I mucked about with a variation so I might as well post my take on it. What y'all might find different is
- that the combos are only as wide as the combo button
- they have an attached/associated label to which I make reference to
- those labels are positioned to the right of the combo, seemingly reversing the side on which the button is located (compared to normal)**
- it is those labels whose backcolor is modified, thus appearing to alter the color of the combo.
- the combo identifying labels on top are what you'd normally see to identify the combo purpose
- there is only 1 line of code per color case, which you might find interesting in that it uses a Hex conversion for the RGB function
- in design view, all combos were selected then simultaneously, their click event was set to be a function name rather than the built in event that you usually would pick

** could have left them as is, but wanted to a) show another trick; b) not show the selected data. If that's desired, then it's easy to modify. Not sure which is right.

There are only 2 functions with 30-odd lines of code (not including spacing lines). To tweak colors, there are 500 hex color values here
Note that changing combo list values such as gray to grey would require the Select Case statements to be edited.
Thanks to davegri for providing the form that I started with, so I do admit to a bit of 'borrowing' - but it doesn't resemble yours too much now so hope that's OK.

BandsMicron.zip

EDIT: just noticed that I left the 2nd procedure as a function instead of a sub. Started out that way & I didn't change it. Figured it was worth noting given my prior statements about subs vs functions. As for the text for the chosen color, not only could the combo width be increased, but another approach could be to set the label caption as well as the backcolor property.

[Micron]

I should mention that I don't often use .ActiveControl or any similar .Active "thing". However, I made an exception in the posted db because the 1st function is called by a control event which I assigned to the onClick property in error. Although it works, I would switch it to AfterUpdate. Regardless, I figure assigning a function rather than having the standard event pretty much guarantees that the desired control is the active one for this form. As long as there's never a prior event that shifts the focus, it should be OK.

Not saying either of these two methods is better, just different. What I do like about the narrow combo is that the list doesn't change color. Forgot to mention that.
Here is how the label caption and its color could be changed using a call to a 3rd procedure rather than duplicating the lines of code for every control. The code changes (including making the aforementioned Function a Sub would be as follows:

Sub PaintLabel(strClr As String, ctl As Control)
Select Case strClr
    Case "black"
        LabelChanges RGB(&H0, &H0, &H0), vbWhite, "black"
        
    Case "brown"
        LabelChanges RGB(&HCD, &H85, &H3F), vbWhite, "brown"
    
    Case "red"
        LabelChanges RGB(&HFF, &H0, &H0), vbWhite, "red"
    
    Case "orange"
        LabelChanges RGB(&HFF, &HA5, &H0), vbBlack, "black"
    
    Case "yellow"
        LabelChanges RGB(&HFF, &HFF, &H0), vbBlack, "yellow"
    
    Case "green"
        LabelChanges RGB(&H0, &HFF, &H0), vbBlack, "green"
    
    Case "blue"
        LabelChanges RGB(&H0, &H0, &HFF), vbWhite, "blue"
    
    Case "violet"
        LabelChanges RGB(&HC8, &H0, &HC8), vbBlack, "violet"
    
    Case "gray"
        LabelChanges RGB(&H64, &H64, &H64), vbWhite, "gray"
    
    Case "white"
        LabelChanges RGB(&HFF, &HFF, &HFF), vbBlack, "white"
    
    Case "gold"
        LabelChanges RGB(&HFF, &HD7, &H0), vbBlack, "gold"
    
    Case "silver"
        LabelChanges RGB(&HCD, &HC8, &HB1), vbBlack, "silver"
    
End Select
    
End Sub

Sub LabelChanges(bgColor As Long, fcColor As Long, strValue As String)
With Me.ActiveControl.Controls(0)
    .BackColor = bgColor
    .ForeColor = fcColor
    .Caption = strValue
End With

End Sub

[accesstos]

@accesstos - your db textbox shows #Name error and the combo doesn't change anything for me. Perhaps I am not using it correctly?

I am so sorry Micron!
After midnight wakes up a daemon in my file browser who choose wrong files. :-D

I replaced the attachment in msg #10 with the correct file.

By the way, I have to inform you that OP is trying to create a resistor calculator.

[moke123]

By the way, I have to inform you that OP is trying to create a resistor calculator.

I had to google it too. https://www.digikey.com/en/resources...or-code-4-band

[davegri]

By the way, I have to inform you that OP is trying to create a resistor calculator.

I also believe that was MadTom's purpose.
Here's the DB that will do exactly that. I liked Micron's narrow comboboxes with labels to show color and included that.
I'm sure there is a way to do this with fewer lines of code, but I was aiming for results with code that was not too obscure (at least to me).
Bands-davegri-v02.zip

Edit: Looking at accesstos' revised DB, my prediction for less code certainly happened!