Auto Name combobox

P

proctk

Well-known Member
Joined
Dec 24, 2004
Messages
840
Hi

I have a worksheet with about 200 embeded list boxes, I need each of them to have uneque name, is there away to create a macro to re name them for me.

in column k there are 31 comboboxs and I want the name to equal "Q & value in column A & SA"

colulm a value = 1
comboboxes start on row 5 in column A

in this case the combobox name would be "q1sa"
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
proctk said:
is there away to create a macro to re name them for me.
*snip*
in this case the combobox name would be "q1sa"
Click to expand...

Is there a way? Yes, I imagine it can be done. You'd have to figure out where the listbox resides on the sheet, based on its Top and Left Properties, and the row height and column width of the sheet.

Try searching the forum and Google as well. You might find something similar to what you want to do.
 
Upvote 0
Hello, proctk,
this
in column k there are 31 comboboxs and I want the name to equal "Q & value in column A & SA"
Click to expand...
can be done with this code
Code: 
Sub rename_comboboxes()
'Erik Van Geit
'050517 0053

Dim sh As Shape
    For Each sh In ActiveSheet.Shapes
      If sh.Type = msoOLEControlObject Then
        If TypeName(sh.OLEFormat.Object.Object) = "ComboBox" And sh.TopLeftCell.Column = 11 Then
        sh.Name = "q" & Cells(sh.TopLeftCell.Row, 1).Value & "sa"
        End If
      End If
    Next sh
End Sub

kind regards,
Erik
 
Upvote 0
Can Column A be blank in any row where there's a combobox? If it is, you might run into problems.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Populating combobox from table based on the header selected in another combobox (some data on different sheets)
Replies
7
Views
225
BWL388
B
M
  • Solved
Using ComboBox Tag property to simply code
Replies
5
Views
375
RoryA
RoryA
K
  • Solved
Change ComboBox list values depending on the selected OptionButton
Replies
2
Views
674
Kakaska
K
S
  • Question
Dynamic dependent ComboBox VBA - error
Replies
3
Views
382
stemby
S
R
  • Question
Dependent Combobox to Reference Worksheet Range Using Offset
Replies
5
Views
391
Akuini
Akuini

Forum statistics

Threads
1,214,424
Messages
6,119,401
Members
448,893
Latest member
AtariBaby

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top