repeating

[Daya100]

Hi,

I'm trying to repeat the code below for
several different cells. Everything stays the same but
the cell (range) changes and msg - there are about 20
different ones. I could just keep writing the if
statement over and over but I know there is a better way.
I was thinking case select but I can't make it work.
Please help!
Amy


Private Sub CommandButton1_Click()

Dim Title As String
Dim result As Variant
Dim cell As Range
Dim msg As String

Title = "Incomplete information"

Set cell = Application.Worksheets("Facility Request&quot.Range
("d25&quot
msg = "Please enter the Date the Facilities are Required."

If IsEmpty(cell) = True Then
result = InputBox(msg, Title)
cell.Value = result
End If

End Sub

 

[SkipVought]

Hi,
Can you define the range of cells that you want to process? It could be a contiguous range (like a column of cells) or non-contiguous, like a collection of cells scattered all ove the sheet. Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

well, they are scattered all over about 10 different sheets. Can I define that as a range?

 

[SkipVought]

Tell me what you are trying to do -- ie the entire process Skip,
metzgsk@voughtaircraft.com

 

[Daya100]

OK - I have this big spreadsheet that people fill out and send to me. Well, they always forget to fill in some cells and the information is required. SO - When they close the workbook I want to run through these 20 cells (on all different sheets in the workbook) and check to make sure they are filled out. And if they aren't then I prompt them for the information and it puts it in the cell. The code I have works - I just don't want to repeat that If statement 20 times - it wouldn't be hard actually but I think there is a better way to do it. Does that make sense?
Thanks!

 

[SkipVought]

Here's what I would do...

1. Assign each cell a Unique Name in the Name Box.

2. On a new sheet, in A1...
Insert/Name/Paste - select Paste List
This is a list of all the names you just created. If you have other names, you will have to flag which ones are to be checked upon Workbook_Close.

3. Parse the Sheet Name, so you have Sheet Name and Name. You will loop thru each SheetName/Name to ...

SHeets(SheetName).Activate
[Name].select

I have go to run now to start my weekend. Give a shout on Monday if you still need help. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

1. As I suggested in the earlier post, Name each data entry cell.

2. Use the following code to check each named range. If you use other named ranges, you will have to accomodate for that in your code (I like to use Select Case Variable...End Select)...

    Dim Name1
    For Each Name1 In Names
        Sheets(Name1.RefersToRange.Worksheet.Name).Activate
        Range(Name1.Name).Select
        MsgBox Name1.Name
    Next

Hope this helps. Skip,
metzgsk@voughtaircraft.com