Free Microsoft Excel 2013 Quick Reference

ActiveX Control combobox and linked cell

Hi -

I am created my combobox using activeX control. In the linked cell properties, I specified the cell.
When I select an option in the combobox I want the linked cell to show the option number rather than the option itself.
Cell Value
D56 4
D57 8
D58 7

For eg:
Linked cell : $D$53
I left the listfillrange blank but wrote a program in VBA to list the values in my combobox
Sub Combobox1_enter()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem (Sheets("Input").Range("D56"))
Me.ComboBox1.AddItem (Sheets("Input").Range("D57"))
Me.ComboBox1.AddItem (Sheets("Input").Range("D58"))
End Sub

If in my combobox I picked 8 (D57), I don't want the linked cell to show 8 but rather I want it to show 2 for second option picked in the list. How do I do this.

Thanks


Hi -

I am created my combobox using activeX control. In the linked cell
properties, I specified the cell.
When I select an option in the combobox I want the linked cell to show the
option number rather than the option itself.
Cell Value
D56 4
D57 8
D58 7

For eg:
Linked cell : $D$53
I left the listfillrange blank but wrote a program in VBA to list the values
in my combobox
Sub Combobox1_enter()
Me.ComboBox1.Clear
Me.ComboBox1.AddItem (Sheets("Input").Range("D56"))
Me.ComboBox1.AddItem (Sheets("Input").Range("D57"))
Me.ComboBox1.AddItem (Sheets("Input").Range("D58"))
End Sub

If in my combobox I picked 8 (D57), I don't want the linked cell to show 8
but rather I want it to show 2 for second option picked in the list. How do I
do this.

Thanks

I'm creating a sheet with 5 checkboxes on each row. These rows are copied down as needed. How can I easily copy a row with checkboxes and have the link cell field in properties automatically link to the underlying field? So the checkbox is in B1 and the Linked cell needs to be B1. I set the color of the cell b1 to white on white so the value TRUE/FALSE does not show. The value of the cell is used elsewhere in the sheet. I tried copying the controls, but the Linked Cell field will either be empty of contain the wrong reference depending whether what was is the source control that was copied.

Hope this is somehow clear. Hard to explain, it would be alot easier if I could show you

I started using the Control Toolbox in Excel to create a form. I wanted a
text box to link to a cell, trying to use an existing form. Strange things
have started to happen with the formulas in the linked cells. The values do
not change with what is entered in the cell. I'm in over my head I am afraid.

Is there a way to keep the integrity of the linked cell and use the Control
Toolbox Text Box in the front?

Thanks in advance,
Carol

Hi,

I have a combobox from the Forms toolbar linked to one cell on my sheet. When the user selects an item from the list, a macro executes and eventually populates a cell directly beneath the combobox's linked cell with results from some formulas. I want to have several comboboxes (with different linked cells) on the same sheet that execute the same macro, where the formula result goes to the cell below the activated combobox's linked cell. Is there a way to determine the linked cell's row and column based on the combobox the user clicks on? Is there an easier way to look at this problem?

Hi,

I am working on something that needs to be turned in today. It'll be great if somebody can help out with this ASAP.

I basically have several "merged cells" and ActiveX Controls (like Checkboxes and Radio Buttons) on my Excel worksheet. I need to be able to "tab" from a "merged cell" to an "ActiveX Control". Currently, although my worksheet IS "PROTECTED", I can only do the reverse, i.e. "tab" from an "ActiveX Control" to a "merged cell" using the command : "Range("A12").Select" in the "keydown" event of the checkbox.

But, it is really important that I need to be able to "tab" from a merged cell to an activex control. It'll be great if somebody can help me with the VBA code for this.

Thanks,
Kns

Hello, I'm a novice at VBA, and I'm trying to write a macro that, among other things, does the following to an ActiveX checkbox:

- changes the Visible property from False to True
- changes the Linked Cell property from "blank" to a reference a particular cell

I've tried the direct approach, ie

CheckBox1.Visible = True

But it doesn't work.

Thanks

In a spreadsheet an ActiveX control button has lost its original
appearance. Originally it looked like a nice rectangle with some text
inside it.
It was located at the top of a spreadsheet (around cell L1). There is
another control button at the top of the same sheet (around cell E1) -
but this one isn't ActiveX, it's created from the form menu and is
linked to a macro.

I used Freeze Frame on cell C5 and noticed a slow degradation in the
appearance of the ActiveX control button as the sheet was scrolled to
the far right.

Anyone have any ideas how to restore the appearance of the ActiveX
control button and how to keep it from changing appearance?

Let me try to describe the current appearance of the ActiveX control
button: text is no longer visible inside the area of the button; the
button's edges are faint; the button is comprised of some seemingly
randomly placed rectangles; the rectangles are different shades of
gray.

Thanks for any suggestions!

Mark

Using Excel 2000

I have created a report sheet that must be filled in on a monthly
basis. There is one column per month. One of the cell is a comment box.
As columns will be hidden/displayed at each month change and for
presentation purposes, I have added a textbox control on the sheet (
not moved nor resized ).

The linked cell reference of the textbox comes from a dynamic name that
returns the reference of comment cell of the reporting month.

I have created the following local named range
comment = OFFSET('Sheet1'!$A$1,0,'Sheet1'!mth_offset)

In the linked cell property of the text box
linked cell = 'Sheet1'!comment

mth_offset is another local named range linked to a spinbutton. As I
spin up and down, the text box correctly displays the content of the
cells in row 1, as you would expect from the 'comment' formula.

The strange thing is that if as soon as I edit the content of the text
box, the dynamic link stops working, i.e. the textbox no longer updates
its content as I spin up/.down.

I have found a workaround: in the spin button change event procedure, I
add the code: "TextBox1.LinkedCell = TextBox1.LinkedCell". With that
the text box updates correctly.

Has anyone faced this problem ?
Is there a workaround that does not rely on code ? - getting users to
activate macros can be a pain.

Thanks for your help.
Regards

I have a table which will be filtered and ordered. I have a checkbox in each row. When the ActiveX control checkbox is selected I want to update a column on the same row to be True or False. The problem is that when the order is changed a checkbox will update the True/False column on a different row - not the row you are selecting. The code below should find the cell address of a checkbox and update the corresponding row in the True/False column.

Private Sub
CheckBox1_Click()
Set Rng = ActiveSheet.CheckBoxes("CheckBox1").TopLeftCell
If CheckBox1 Then
ActiveSheet.Range(Rng.Address).Value = "TRUE"
Exit Sub
End If
ActiveSheet.Range(Rng.Address).Value = "FALSE"
End Sub

I'm getting "Unable to get the CheckBoxes property of the Worksheet class" right now.

I need help with the use of a check box and linking cells via format control. Or maybe I should use a macro???? I am creating a workbook for other users to enter data. This workbook will include 1 worksheet for each day of the month and then a worksheet for tallying some of the input. On the daily worksheets, I have several check boxes which link to the tally worksheet through the format control. I have only created days 1-7 and am wondering if there is a faster way to link the cells then redoing each one individually. I tried to copy and paste the worksheet from 1 day to the next, but it doesn't automatically update the cell link in the control format to the next row on the tally sheet. I have attached a workbook sample. I have highlighted the check boxes, and if you click on the checkbox control and then look at the cell link and follow to the tally worksheet, I think you'll see what I'm trying to do. Any help would be GREATLY APPRECIATED as I'm getting desperate.

Hey Guys, need some help please!

I am trying to create a spreadsheet where if a there is a value in one column for instance column A, it creates a checkbox in column B and link the controls to that same cell it is created in B.

I want to use the true or false result for an other column with an equation or two.

Regards,
Phil

I'm new to using controls. Is there a way to have something triggered by looking at the check box control instead of linking a cell to the check box control. IE, refer to a named checkbox and avoid having a cell dependent on a check box and using that dependent cell to trigger events.

I need help urgently....i'm on a deadline of COB today...if anyone could tell me how to link cells from one workbook to another and being able to update figures using the refresh (!) button???

Thanks So Much
Natalie

Hi there,

Seriously needing you help,
how would I be able to specify the the Cell Range and the Linked Cell to the listbox using user forms.

Many thanks,
Stan

I am trying to manipulate some embedded activeX controls
(comboBox) on a worksheet. Refer some sample coded below:

Dim w As Worksheet
Set w = ActiveWorkbook.Worksheets("help")
ActiveWorkbook.Worksheets("help").cboX.AddItem = "test"
w.cboX.AddItem = "again"

Line 3 works fine, and adds the text "help" to the combo
box.

Line 4 which I would have thought was identical, fails
even to compile!

Due to the nature of the task at hand it will be much
neater if I can get the code given (or like) line 4 to
work. Any ideas?

Thanks

Jack

Hello all, and thank you in advance for your help with this odd question:

I have a worksheet that started out using all Form Control ComboBoxes. I
started switching over to ActiveX Control comboboxes to give me additional
VBA control. While my sheet was in a state of transition, I happened to
notice that the text appearing in the Form Control ComboBoxes appeared
properly while that appearing in the ActiveX combobox did not. Instead, the
text appearing in the latter had letters running together slightly or too far
appart (as if kerning was all messed up) and just looked plain ugly. (I wish
I could attach a screen capture.)

I have made sure both types are using the same font & size (Tahoma, 8pt).

What gives? I need the ActiveX ComboBoxes but I am not happy that the text
is going to look like .... uh, you know.

Any suggestions?

I need to transpose and link the contents of cells from one worksheet onto
another worksheet but I can't seem to do this with the paste special command.
If I select transpose it greys out link cells. Thanks in advance.

Hello

I am new to the formum and just getting into more VB thru Excel and I am looking for a little help please.

I read multiple threads/posts on using pictures within excel.

I have the image lookup working great using this code based on http://www.mcgimpsey.com/excel/lookuppics.html.

Private Sub Worksheet_Calculate()
        Dim oPic As Picture
        Me.Pictures.Visible = False
        With Range("F1")
            For Each oPic In Me.Pictures
                If oPic.Name = .Text Then
                    oPic.Visible = True
                    oPic.Top = .Top
                    oPic.Left = .Left
                    Exit For
                End If
            Next oPic
        End With
End Sub
However, now when I go to insert an activex control Combobox I start getting errors.
Run-time error '13' to be specific.
Plus my combobox disappers.

If I go back into design mode and delete combobox the issue goes away.

I am looking to add multiple activex comboboxes and would appreciate any help.

Thanks
Cbell

Hi,

I want to Transpond and fast link cells with drag function in bottom right corner of an cell.

Example:
Stand in cell A1, enter "=C1"uses the "drag link cell" in the down right corner of a cell. I will automaticly link
A1 - C1
A2 - C2
A3 - C3 ect

My question is if it is possible to drag horisonal and link vertically.

A1 - C1
A2 - D1
A3 - E1

Are the any button command that i can ues/press so this is done automatically or an easy funtion?

Appreciate your help!

Hello everyone,

I'm new here and a beginner of VBA and macros. I'm wondering if anyone ever experienced same problem as I do and finally got a solution to it.

In the Excel template I'm working on, there are dozens of hidden columns in one worksheet. Each column contains a Checkbox (ActiveX Control) in a certain cell. These columns are only unhidden when needed. And those Checkboxes can stay in the cells as they are supposed to when unhidden in Excel 2007. But this is not the case using Excel 2010. When I unhide any of those columns, Excel 2010 will re-position all the originally hidden checkboxes to the nearest unhidden column. And the column(s) just unhidden will have no checkbox in it. Running macro codes to unhide them will cause the same problem. But if no unhiding action is performed, Excel 2010 won't do anything to those hidden checkboxes.

It looks like an Excel 2010 bug to me. But I found few discussion about it on the internet. Could anybody here help me out?

Thank you very much for your time!

I added a calendar control to a sheet so the user can select the beginning date of a range. The LinkedCell value points to a cell on the same sheet. A formula on another sheet does a VLookup of that value. If I type a date into the cell, the VLookup works fine. If I select the date from the calendar control, it fails. Apparently the control enters the date into the linked cell as a text value (m/d/yyyy format) instead of a date value. I've confirmed this by changing the format of the cell to another format, but if I select a date from the calendar control, it still appears in the cell as m/d/yyyy. Is there a way to get the date from the control into the linked cell as a date value? I tried finding an event where I could correct it, but there is no event late enough in the process to capture the new value.

Hi all, I could use some help in working out how the nora you stop Excel doing this? I have a multipage Workbook and link cells (in order to link Written information, not numerical) The problem occurs when i have to add a new line of info into one of the worksheets! The Link links to the same cell but not to the info as that has shifted one line down!

How do i get the link to stick to the info in the cell NOT the cell itself?

I hope i have made that clear? not easy Grin. D x

My Activex controls (spinners and a scroll bar) move to a different location
on the worksheet each time I do Print Preview and if I change the page
orientation. Is there a property that locks them in place?

The matter is not new, was searching on Internet but still cannot find the answer
In Excel 2010 when you create an activex controls, place them in cells, hide rows, save the workbook, close it, open again then unhide the rows which contained controls, controls disappear.
it seems to be a bug. Does anybody know if Microsoft is intending to fix it ?

regards