About Us

Teresse

I am forever grateful for the help!

Teresse

"JLatham" wrote:

Options are always a good thing! Good of you to put the code up for potential
use.

"OssieMac" wrote:

Hi Teresse,

You might find the reply from JLatham a better option but since I have
created this for you then here it is and you now have a choice.

Make a backup copy of your workbook just in case the macro does not perform
as you expect. A lot of instructions so it might be worth printing them but
keep the internet site open because you have to copy macros from it.

With Excel 2007, you will need the Developer tab in the ribbon and also
macros enabled.
To display Developer tab in ribbon (if not already displayed):-
Select the Microsoft button (Large button top left)
Select Excel options (Towards bottom right of dialog)
Should default to Popular (Left column). If not then select Popular.
Under €˜Top options for working with Excel check €˜Show Developer tab in the
ribbon)

To Enable macros to run:-
Select the Microsoft button (Large button top left)
Select Excel options (Towards bottom right of dialog)
Select Trust Center (Left column)
Select Trust Center Settings (Middle right of dialog)
Select Macro Settings (Left column)
Select disable all macros with notification.

The first macro that you are going to run creates a worksheet named
ComboFill and inserts some dummy data for the Drop Down Combo Box. It then
creates the Drop Down Combo Box without data. You will then need to edit the
dummy data and replace it with the data you want in the drop down box.

Open the workbook and select the worksheet where you want the dropdown combo
box.

Alt/F11 to open the VBA editor.

Select menu item Insert-Module. (A white area on the right opens)

When you copy the macros into the editor, any lines prefixed with a single
quote and displayed in green are comments and do not form part of the actual
code.

Copy the following macro and paste it into the white area of the VBA editor.
(Ensure that you copy it all from Sub Combo_Create() to End Sub inclusive.)

Sub Combo_Create()
Dim objCombo As OLEObject
Dim rngCombo As Range
Dim wsData As Worksheet
Dim i As Long

'Edit the sheet name enclosed in double quotes
'to suit your data worksheet name.
Set wsData = ActiveSheet

'Test for existance of worksheet 'ComboFill'
'by attempting to select and then
'create the worksheet if non existent.
On Error GoTo CreateWorkSheet
Sheets("ComboFill").Select 'Error if non existant
On Error GoTo 0 'Cancel On Error routine

wsData.Select
With wsData
Set objCombo = .OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=288, _
Top:=102, _
Width:=100, _
Height:=26)
End With

'Name the ComboBox
objCombo.Name = "MyCombo"

'Hide the combo box
objCombo.Visible = False
Exit Sub 'Exit without entering CreateWorkSheet
CreateWorkSheet: 'Only if does not exist
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "ComboFill"
Range("A1") = "Code & Description"
Range("A2") = "A - Leave type 1"
Range("A3") = "B - Leave type 2"
Range("A4") = "C - Leave type 3"
Range("A5") = "D - Leave type 4"
Range("A6") = "Cancel -"
Columns("A:A").Columns.AutoFit

Resume Next 'Return to line after error

End Sub

Place the cursor anywhere within the macro.

Press F5 ONCE ONLY. (You wont see anything happen but it will have created
a worksheet named ComboFill and created the combo box and hidden it.)

Alt/F11 to return to worksheets.

Select the newly created worksheet ComboFill.

Edit the data and insert the characters and descriptions that you want. The
data must have a space and hyphen between the code and the description. Do
not remove the last item €˜Cancel €“€˜. You can have more or less items but
Cancel €“ should be the last item.

Make a note of the cell reference of the last cell of the list. (That is
where €˜Cancel €“ €˜ is entered.)

Change back to the worksheet where you have your data. (This is important or
the next macro will fail.)

Alt/F11 to open the VBA Editor again.

Copy the following macro and paste into the editor below the previous macro.
(From Sub Populate_Combo() to End Sub inclusive)

Sub Populate_Combo()

With ActiveSheet.MyCombo
.ListFillRange = Worksheets("ComboFill") _
.Range("A2:A6").Address(external:=True)
End With

End Sub

Towards the end of the macro where it says .Range("A2:A6"), edit the A6 to
the match the last cell of the data in worksheet ComboFill. (The reference
you made a note of above)

Click anywhere within the macro between Sub Populate_Combo() and End Sub.

Press F5 ONCE ONLY.

Alt/F11 to return to worksheets.

Select your data worksheet.

Right click on the tab for your data worksheet name.

Select View Code. This time the VBA editor will open in the Worksheet area.
(You can see the worksheet name highlighted in Project Explorer in the left
column.)

Copy the following TWO macros and paste them into the VBA Editor. (Can copy
and paste both together)

Private Sub MyCombo_Click()
Dim varCombo As Variant 'User selection in Combo Box
Dim nbrChars As Long 'Number of characters to extract
Dim strCode As String 'Character extracted

'Assign selected value to a variable
varCombo = ActiveSheet.MyCombo.Value

'Find numeric position of hyphen sign
nbrChars = InStr(1, varCombo, "-") - 1

'Assign code character to a variable and
'trim any superflous spaces.
strCode = Trim(Left(varCombo, nbrChars))

'Option if user selects cancel.
'If not cancel, insert selected code
If strCode < "Cancel" Then
ActiveCell = strCode
End If
ActiveSheet.MyCombo.Value = ""
ActiveSheet.MyCombo.Visible = False

End Sub

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim rowNbr As Long 'Current column number

rowNbr = Target.Row
Select Case rowNbr
Case 6 'Only if double click in row 6

'Double click in a cell places in Edit Mode
'Therefore Cancel edit mode for the cell
Cancel = True

ActiveSheet.MyCombo.Visible = True

Case Else
'Do nothing if not one of the above rows
End Select

End Sub

Close the VBA editor. (Click on the Cross in the Red background top right)

Double click in a cell in row 6 and the drop down will be displayed. Make a
selection and the cell will be updated.

Note it only works when you double click on a cell in row 6.

Feel free to get back to me if you have any problems.

Regards,

OssieMac

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Switch to Hybrid Mode Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Insert and sum in empty cell	salsal	Excel Worksheet Functions	4	July 5th 06 04:17 PM
how insert same text in empty cells in column (10000 rows)	bromptongadgets	Excel Worksheet Functions	1	December 11th 05 03:13 PM
Need a macro to insert text in a cell that already has text.Excel	go1angel	Excel Discussion (Misc queries)	2	October 5th 05 10:32 PM
Insert text in a cell with a macro	kayabob	Excel Discussion (Misc queries)	5	June 22nd 05 05:43 PM
Need macro to insert text string while inside cell (formula)	BrianB	Excel Discussion (Misc queries)	0	May 31st 05 03:18 PM

Menu

About Us