Combobox Help

R

Ryan

I have an area on my spreadsheet where users input their data and then get an output near the bottom of the page. I want to make the navigation of the input part as easy as possible as there will be people who are not very computer literate using this spreadsheet

The problem that I am having is being able to tab into a combo box. I found code from someone else that helps me tab through the form once I have gotten inside a combo box, but I don't know how to get into the first combo box. For instance, I have a combo box (combobox6) sitting on top of cell D9. I want to be able to tab from cell L8 directly into the combo box. Right now I tab from cell L8 to cell D9. Once inside of combobox6, I am able to tab through the rest of the form the way that I want

If anyone out there can help me find a way to tab into combobox6 from a cell I would appreciate it

Rya
 
D

Doug Glancy

Ryan,

I've spent some time the last couple days trying to figure something out for
you. I'm hardly an expert, but I've looked at all the methods and
properties for a dropdown box from the Forms toolbar and I can't figure out
how to tab into one. If you were using comboxes from the Controls toolbar
it seems to be as simple as "Combobox1.Activate." Maybe you should consider
switching? (I take it you can't use my earlier suggestion of using Data
Validation in worksheet cells).

Just wanted to let you know I've been trying...

Doug

Ryan said:
I have an area on my spreadsheet where users input their data and then get
Click to expand...
an output near the bottom of the page. I want to make the navigation of the
input part as easy as possible as there will be people who are not very
computer literate using this spreadsheet.
The problem that I am having is being able to tab into a combo box. I
Click to expand...
found code from someone else that helps me tab through the form once I have
gotten inside a combo box, but I don't know how to get into the first combo
box. For instance, I have a combo box (combobox6) sitting on top of cell
D9. I want to be able to tab from cell L8 directly into the combo box.
Right now I tab from cell L8 to cell D9. Once inside of combobox6, I am
able to tab through the rest of the form the way that I want.
 
K

Kevin Beckham

If the object is called ComboBox6 then it most probably is
a control, so add this code to the code pane for the
worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Static lastAddress As String

If lastAddress = "$L$8" Then
ComboBox6.Activate
End If
lastAddress = Target.Address
End Sub

This code traps any movement out of the cell L8

Kevin Beckham
 
R

Ryan

Doug

I just reread your data validation suggestion. I am not quite sure how to accomplish this. If you could help me that would be great

At this point, I have pretty much given up on having the comboboxes automatically drop down. I just want to figure out a way to tab from a cell into one. If I can accomplish that then I will be satisfied

Ryan
 
D

Doug Glancy

Ryan,

Look up Data Validation in Excel Help (not VBA Help) and in Google Groups
for more info. Data Validation is found in the Excel Data menu. Validation
applies directly to spreadsheet cells, no controls or programming required.
It enables you to specify a list of allowed entries in a given cell. You
can have unique validation for each cell or apply the same validation list
across several cells, rows, etc. The list can be specified in the Data
Validation dialog box or, probably better, you can specify a range on the
worksheet that contains your list. You can specify a range in another sheet
or workbook, but you have to use a named range (Insert -> Name->Define).

From an earlier post I had assumed that the comboboxes you're trying to use
are from the Forms Toolbar. If you use the ones from the Control Toolbox,
you can use the Activate method. In order for it to be automatic, you'd use
a worksheet event, as Kevin discussed. Worksheet events are entered in the
worksheet code module in the VB Editor. To view the worksheet code module,
right-click the sheet tab and choose "View Code." In the dropdown box at
the top left of the code window choose "WorkSheet". In the dropdown box at
the top right, you will then see all the worksheet events, including the
SelectionChange event.

The following code is a SelectionChange event that you can paste into this
window. It will put the cursor in ComboBox1 (from the Control Toolbox) when
you tab to or click cell "L8": (I used ActiveCell instead of Target so it
won't fire if the user selects multiple cells that include L8.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("L8")) Is Nothing Then
ComboBox1.Activate
End If

End Sub

hth,

Doug

Ryan said:
Doug,

I just reread your data validation suggestion. I am not quite sure how to
Click to expand...
accomplish this. If you could help me that would be great.
At this point, I have pretty much given up on having the comboboxes
Click to expand...
automatically drop down. I just want to figure out a way to tab from a cell
into one. If I can accomplish that then I will be satisfied!
 
D

Doug Glancy

Ryan,

I'm glad it worked! I realized there was one more part of your question
that I can answer. If you change the code to this, it will show the
dropdown list. Also, with the .Listindex line you can have it display a
default value from the list (0 = first item, 1 = 2nd, etc.)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(ActiveCell, Range("L8")) Is Nothing Then
With ComboBox1
.Activate
.DropDown 'show the dropdown
.ListIndex = 0 '0 is 1st list item
End With
End If

End Sub


I think that's it...

Doug

Ryan said:
Doug,

You are my hero! That bit of code did the trick! You have helped me out
Click to expand...
so much. Not only did you fix my dilema, but you unlocked a huge problem
that I was having in trying to learn VBA.
 
Top