Art,
I think this will get you started. First, what we will do is use some VBA
code to create two special macros that will both work automatically. One
will be associated with the description entry sheet, while the second one
will be used with the other sheet in the workbook to create your drop down
list.
You said that you'd like the "word processing window" to pop up when a
description cell is clicked. We will assume that all cells on row 2 on that
one sheet are description cells. Otherwise we will need some other
definition of what determines whether a cell is a description cell or not.
Here is the first macro. It has to go in the worksheet's code segment.
Getting it there is easy enough:
Choose the sheet where your math & description cells will be and right-click
on that sheet's name tab and choose [View Code] from the popup list that
appears. Simply copy the code below and paste it into the code window that
appears and then you can close the Visual Basic (VB) Editor.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'do not do anything if:
' cell selected is not in the description row (row 2),
' or if the cell is not empty (already contains description),
' or if there is more than one cell selected
'
'change this Const to whatever row your description cells
'will be in.
Const descriptionRow = 2
If Target.Row <> descriptionRow Or _
Not IsEmpty(Target) Or _
Target.Cells.Count > 1 Then
Exit Sub
End If
Target.Value = InputBox("Enter Description:", "New Description")
'you can now use Excel's own spell checking to check entries on the sheet.
'by pressing [F7] or using Tools | Spelling from the main Excel menu.
End Sub
Now for the second piece of code. This time choose the sheet that you want
the drop-down list to appear on. Again right-click the sheet's name tab and
choose [View Code] from the list. Copy the code below and paste it into the
code module present to you this time and then edit the code to tailor it to
your situation. You'll probably need to change 2 of the Const values:
Those would be sourceSheetName which must be the same name as the sheet
where you have the descriptions entered. The other would be cellForList -
this should be the address where you want the list to appear in. Also,
sourceSheetDescriptionRow must be the same as defined by
Const descriptionRow = 2
in the code above.
Private Sub Worksheet_Activate()
Const sourceSheetName = "Sheet1"
Const sourceSheetDescriptionRow = 2
Const cellForList = "A1" ' on this sheet
Const listSourceName = "descriptionList"
Dim sourceSheet As Worksheet
Dim listAddresses As String
Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
'this assumes that all cells in row 2 are filled
'as a list beginning at column A and continuing as
'an unbroken list to the last entry in whatever
'column that may be
listAddresses = "A" & sourceSheetDescriptionRow & ":" & _
sourceSheet.Range("A" & sourceSheetDescriptionRow).End(xlToRight).Address
ActiveWorkbook.Names.Add Name:=listSourceName, RefersTo:= _
"='" & sourceSheetName & "'!" & listAddresses
With Me.Range(cellForList).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & listSourceName
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Set sourceSheet = Nothing 'release resources
End Sub
after you've pasted the code in and made necessary changes, it should all
work for you. You'll have to set up some descriptions on the first sheet (at
least one) and then once you've done that when you choose the other sheet,
its list will be updated automatically.
I hope this helps you get the job done.
Skip cell with dates said:
Hi,
I'm looking to have a window open or pop up to enter text in a cell.
If cell A1 is Math and A2 is the discription when I click on A2 or any
discription cell I would like a word processing window to pop up. Be able to
enter text, spell check and have it placed in that cell. Now, if I'm not
asking enought I would like to have the entry saved to some kind of drop down
menu for reuse on another work sheet.
If i'm asking too much don't get mad. I'm new at this.
Thanks,
art