pop up window

[Skip cell with dates]

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

 

[JLatham]

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.

 

[Art]

Wow thanks,

The first part of this really incredible code I got seems to work pretty
well. The cells I need to enter a description in are rows 1,2,3,5,6,7. I
would like to be able to use the return or enter key for wrap around text.

The second part you lost me. The main worksheets are labeled with dates
9-1-08, 9-8-08 etc.
the worksheet were the descriptions go is labeled wooksheet1.

This code is way over my head. I'm very impressed.

Thanks so much,
Art

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.

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

 

[JLatham]

Art,
Having provided a basic solution, from here on out it gets very specific to
your setup and I think it will be more productive for you if you get in touch
with me direct contact with me and we can work toward a better solution for
you.

There are limits to what we can do with lists and data validation (the
method I use to come up with the drop-down list on the second sheet), and
having multiple (date-named) sheets is going to add some complexity to
building up the drop down lists.

So if you would like to, send an email to (remove spaces)
HelpFrom @ jlathamsite .com
with a copy of your workbook attached and we'll see where we can go from here.

I'll probably have to ask some questions about things like 'what' and
'where' and the often so important "why - what is the purpose of these
entries". We can then look to come up with a solution that works as well as
possible for you.

Wow thanks,

The first part of this really incredible code I got seems to work pretty
well. The cells I need to enter a description in are rows 1,2,3,5,6,7. I
would like to be able to use the return or enter key for wrap around text.

The second part you lost me. The main worksheets are labeled with dates
9-1-08, 9-8-08 etc.
the worksheet were the descriptions go is labeled wooksheet1.

This code is way over my head. I'm very impressed.

Thanks so much,
Art

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.

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

 

[JLatham]

Art,
Please send me an email: system with email on it crashed hard, so I don't
have your email address available right now.
I have changes ready to send to you 9/9/2008
Jerry