Macro to insert text into empty cell
 

Hi,

I am trying to insert a button on a excel sheet which would insert a single
letter into an empty cell when clicked. I am having trouble with the macro
command. This is a first for me and I am stuck. Basically, it's an attendance
calendar, so I need several buttons to represent different letters (S,P,
etc). I know there is a way to click on the cell, then click on a button
representing the corresponding letter needed to fill in the cell, I just
don't know how to word it in macros. Any and all help would be deeply
appreciated. THANK YOU

Macro to insert text into empty cell
 

Hi Teresse,

What about a change of method. When you double click on a cell you are
presented with a drop down list to select the required character. You could
have the letter and a description for the letter in the dropdown. Example:-

S - Sick leave.

The macro could just insert the one character or say all characters before
the hyphen sign if more than one character required.

Can include a cancel in the dropdown so that it leaves the cell as is in
case you double click by error and it gives you a way out.

If you want this then I need some more info.

What version of excel are you using? Will work with all versions since XP
(2002). Not sure about versions before that.

It is best if the worksheet can be set up so that the inserts are in a
specific column or columns. I need to know what the columns are. Also by
having specific columns the macro will only run when a cell in the specified
column/s is clicked.

A list of the options for the dropdown list (including descriptions if you
want them.)

Do you want the user to be able to add additional options for the dropdown
list?

There might be more questions before it is finished. It might sound complex
but it isn't really and I am prepared to do the work for you if you want it.

Regards,

OssieMac

Macro to insert text into empty cell
 

A macro really is not needed. Consider using a Data Validation drop-down.
--
Gary''s Student - gsnu200752


"Teresse" wrote:

Hi,

I am trying to insert a button on a excel sheet which would insert a single
letter into an empty cell when clicked. I am having trouble with the macro
command. This is a first for me and I am stuck. Basically, it's an attendance
calendar, so I need several buttons to represent different letters (S,P,
etc). I know there is a way to click on the cell, then click on a button
representing the corresponding letter needed to fill in the cell, I just
don't know how to word it in macros. Any and all help would be deeply
appreciated. THANK YOU

Macro to insert text into empty cell
 

First, thank you for the respose.

I tried a drop down menu but there is just no room. I have 1 sheet make with
a full year so the cells are quite small. If there is a way to double click
on the cell to show a drop down selection, that may work fine. For example,
January dates run from G5 across to AK5. there is a row below of empty cells
(G6-AK6) for a single letter representing the reason for absence.

If the is possible, instructions would be extremely helpful. Once again, I
do appreciate all the help!

Mahalo,
Teresse


"Teresse" wrote:

Hi,

I am trying to insert a button on a excel sheet which would insert a single
letter into an empty cell when clicked. I am having trouble with the macro
command. This is a first for me and I am stuck. Basically, it's an attendance
calendar, so I need several buttons to represent different letters (S,P,
etc). I know there is a way to click on the cell, then click on a button
representing the corresponding letter needed to fill in the cell, I just
don't know how to word it in macros. Any and all help would be deeply
appreciated. THANK YOU

Macro to insert text into empty cell
 

FYI-Sorry, forgot to include I'm working with Excel 2007

"Teresse" wrote:

Hi,

I am trying to insert a button on a excel sheet which would insert a single
letter into an empty cell when clicked. I am having trouble with the macro
command. This is a first for me and I am stuck. Basically, it's an attendance
calendar, so I need several buttons to represent different letters (S,P,
etc). I know there is a way to click on the cell, then click on a button
representing the corresponding letter needed to fill in the cell, I just
don't know how to word it in macros. Any and all help would be deeply
appreciated. THANK YOU

Macro to insert text into empty cell
 

Teresse, I took Gary''s Student's suggestion and have prepared a
sample/tutorial workbook to show you how to do it in 2007. Click the link
below and save the .zip file to your system, open the file and extract the
..xlsx file from it to see how to set up Data Validation using a list of
entries on a separate sheet in the workbook.

http://www.jlathamsite.com/uploads/A...Validation.zip


"Teresse" wrote:

FYI-Sorry, forgot to include I'm working with Excel 2007

"Teresse" wrote:

Hi,

I am trying to insert a button on a excel sheet which would insert a single
letter into an empty cell when clicked. I am having trouble with the macro
command. This is a first for me and I am stuck. Basically, it's an attendance
calendar, so I need several buttons to represent different letters (S,P,
etc). I know there is a way to click on the cell, then click on a button
representing the corresponding letter needed to fill in the cell, I just
don't know how to word it in macros. Any and all help would be deeply
appreciated. THANK YOU

Macro to insert text into empty cell
 

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

Macro to insert text into empty cell
 

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

Macro to insert text into empty cell
 

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