EXCEL 97 a couple of problems Questions

Stuart,

RE: Q1 - You haven't provided much info on the problem but here goes. You can gain program control when a user clicks on a cell by using the Worksheet_SelectionChange event

The following macro could run on the "menu" worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Column = 1 And Not (IsEmpty(Target)) Then

For Each i In Worksheets("Bill").Range("A:A")
If IsEmpty(i) Then
' This is a blank cell and can be written to

Worksheets("Bill").Cells(i.Row, i.Column).Value = Target.Value
Worksheets("Bill").Cells(i.Row, i.Column).Offset(0, 1).Value = Target.Offset(0, 1).Value
Exit For
End If
Next

End If
End Sub

In English: if a value exists in column A of the "menu" worksheet then
move the value in column A to the next blank cell in column A of the "bill" worksheet
move the value in one column to the right of column A but on the same row to one column to the right of column A in the "bill" worksheet.

Obviously this is a very simplistic (and a bit flaky) view of your problem but may provide a foundation or some ideas.

RE: Q2 - This is the simplest way to open a word document. If you need to do more to the document once opened I would suggest DDE

In the Worksheet_change event of the relevant worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim DocName As Variant
Dim AppPath As Variant

DocName = "C:\WARNING.doc" ' Put your document path here
AppPath = "C:\apps32\MsOffice\Office\winword.exe" ' put your MSWord path here

If Target.Row = 1 And Target.Column = 1 Then
' We are in cell A1
If Target.Value = 1 Then

Shell (AppPath & " " & DocName)

End If
End If

End Sub

Hope this is of some help.

Follow-up questio/Answer for you all and to LEROY

Thanks Leroy This is helping a great deal, Just to let you know a bit more detail the user at the moment use Tick/Check Boxs can the infor you provide me assign to these check boxes. If Check Boxes cannot be used how about Buttons if not then it will have to be on work sheets.

Sorry about this but my ICT Manager/Co-ordinator would like the use of Buttons/Check Boxes but and easy the better as the Pupils do not have deals with VB if fact if we told them thay would be coding dirrect then i think most of them would get up and walk out.

Thanks
Stuart Stuart, RE: Q1 - You haven't provided much info on the problem but here goes. You can gain program control when a user clicks on a cell by using the Worksheet_SelectionChange event The following macro could run on the "menu" worksheet: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Target.Column = 1 And Not (IsEmpty(Target)) Then For Each i In Worksheets("Bill").Range("A:A") If IsEmpty(i) Then ' This is a blank cell and can be written to Worksheets("Bill").Cells(i.Row, i.Column).Value = Target.Value Worksheets("Bill").Cells(i.Row, i.Column).Offset(0, 1).Value = Target.Offset(0, 1).Value Exit For End If Next End If

Re: Follow-up questio/Answer for you all and to LEROY

Stuart,

The macros can be used by checkboxes, however a checkbox does not automatically relate to a cell. You should assign a "linked cell" to the checkbox using the checkbox properties (right click on checkbox). This "linked cell" will return either TRUE if the checkbox is checked or FALSE if it isn't. Using the linked cell you can work out what data needs to be moved (ie: data in the same row).
The CheckBox1_Click event will control the movement of data. Remember: you will need to LINK each checkbox to its corresponding worksheet cell.

Macro Example:

Private Sub CheckBox1_Click()

Dim FoodType As Variant
Dim FoodPrice As Variant

If CheckBox1.Value Then


FoodType = Range(CheckBox1.LinkedCell).Offset(0, 1).Value
FoodPrice = Range(CheckBox1.LinkedCell).Offset(0, 2).Value

For Each i In Worksheets("Bill").Range("A:A")

If IsEmpty(i) Then

' This is a blank cell and can be written to
Worksheets("Bill").Cells(i.Row, i.Column).Value = FoodType
Worksheets("Bill").Cells(i.Row, i.Column).Offset(0, 1).Value = FoodPrice

Exit For

End If
Next

End If

End Sub

Again, this is a simple exmaple that does not cater for duplicate entries etc.

It sounds like your boss does not want you to use VBA, however I cannot think of a non-coded way of doing this. Maybe someone else in the forum can help you out with a non-code solution.

HTH.

For Each i In Worksheets("Bill").Range("A:A") If IsEmpty(i) Then ' This is a blank cell and can be written to Worksheets("Bill").Cells(i.Row, i.Column).Value = Target.Value Worksheets("Bill").Cells(i.Row, i.Column).Offset(0, 1).Value = Target.Offset(0, 1).Value Exit For End If Next End If

FOR ATTEN: OF LEROY

Thanks Leroy,
You are being of great help and My boss says thanks.

I was wondering the check box macro does not seem to work and I might be missing something.

could you possible send me a demo speadsheet so that I can see it working
my home email is
stuart@rama1712.com
if possible can I have a email address for you so that I can Email you when all this info compiled and working "I HOPE" Stuart, The macros can be used by checkboxes, however a checkbox does not automatically relate to a cell. You should assign a "linked cell" to the checkbox using the checkbox properties (right click on checkbox). This "linked cell" will return either TRUE if the checkbox is checked or FALSE if it isn't. Using the linked cell you can work out what data needs to be moved (ie: data in the same row). For Each i In Worksheets("Bill").Range("A:A") If IsEmpty(i) Then ' This is a blank cell and can be written to Worksheets("Bill").Cells(i.Row, i.Column).Value = Target.Value Worksheets("Bill").Cells(i.Row, i.Column).Offset(0, 1).Value = Target.Offset(0, 1).Value Exit For End If Next End If