| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
cells mandatory to fill
I am creating a file in where if a user fills one column, other three columns
should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
If a cell has anything in it including a formula or a null string, it isn't
empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
Hi, then which command could I use so that when there is a null string the
user get's a message that some info is missing? And how should this be inputted inside VB? "Tom Ogilvy" wrote: If a cell has anything in it including a formula or a null string, it isn't empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
this is the code I am currently using:
Dim cell As Range For Each cell In Sheets("X340").Range("T4,T5,T6,T7,T8,T9,T10,T11,T1 2,T13,T14,T15,T16,T17,T18,T19,T20,T21,T22,T23,T24, T25,T26,T27,T28,T29,T30,T31,T32,T33,T34,T35,T36,T3 7,T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49, T50,T51,T52,T53") If IsEmpty(cell.Text) Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell hope this helps! "Tom Ogilvy" wrote: If a cell has anything in it including a formula or a null string, it isn't empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
Dim cell As Range
For Each cell In Sheets("X340").Range("T4:T53") If len(trim(cell.Text)) = 0 Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell -- Regards, Tom Ogilvy "leonardo" wrote in message ... this is the code I am currently using: Dim cell As Range For Each cell In Sheets("X340").Range("T4,T5,T6,T7,T8,T9,T10,T11,T1 2,T13,T14,T15,T16,T17,T18, T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T3 1,T32,T33,T34,T35,T36,T37, T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49,T5 0,T51,T52,T53") If IsEmpty(cell.Text) Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell hope this helps! "Tom Ogilvy" wrote: If a cell has anything in it including a formula or a null string, it isn't empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
thanks! it worked, one question how do I do so it does the same on three
different worksheets. My worksheets are named as follows: X340, X342n and X642e? "Tom Ogilvy" wrote: Dim cell As Range For Each cell In Sheets("X340").Range("T4:T53") If len(trim(cell.Text)) = 0 Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell -- Regards, Tom Ogilvy "leonardo" wrote in message ... this is the code I am currently using: Dim cell As Range For Each cell In Sheets("X340").Range("T4,T5,T6,T7,T8,T9,T10,T11,T1 2,T13,T14,T15,T16,T17,T18, T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T3 1,T32,T33,T34,T35,T36,T37, T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49,T5 0,T51,T52,T53") If IsEmpty(cell.Text) Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell hope this helps! "Tom Ogilvy" wrote: If a cell has anything in it including a formula or a null string, it isn't empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
cells mandatory to fill
Dim cell As Range Dim sh as Worksheet for each sh in Worksheets(Array("X340", "X342n","X642e")) For Each cell In sh.Range("T4:T53") If len(trim(cell.Text)) = 0 Then MsgBox "There is data missing" & cell.Address Application.Goto cell, True Cancel = True Exit For End If Next cell Next Sh -- Regards, Tom Ogilvy "leonardo" wrote in message ... thanks! it worked, one question how do I do so it does the same on three different worksheets. My worksheets are named as follows: X340, X342n and X642e? "Tom Ogilvy" wrote: Dim cell As Range For Each cell In Sheets("X340").Range("T4:T53") If len(trim(cell.Text)) = 0 Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell -- Regards, Tom Ogilvy "leonardo" wrote in message ... this is the code I am currently using: Dim cell As Range For Each cell In Sheets("X340").Range("T4,T5,T6,T7,T8,T9,T10,T11,T1 2,T13,T14,T15,T16,T17,T18, T19,T20,T21,T22,T23,T24,T25,T26,T27,T28,T29,T30,T3 1,T32,T33,T34,T35,T36,T37, T38,T39,T40,T41,T42,T43,T44,T45,T46,T47,T48,T49,T5 0,T51,T52,T53") If IsEmpty(cell.Text) Then MsgBox "There is data missing" & cell.Address Application.Goto cell Cancel = True Exit For End If Next cell hope this helps! "Tom Ogilvy" wrote: If a cell has anything in it including a formula or a null string, it isn't empty. -- Regards, Tom Ogilvy "leonardo" wrote in message ... I am creating a file in where if a user fills one column, other three columns should be filled also. Now, I have created a formula in where it checks that all colums are filled; therefore, if one column has been filled and others are missing it will tell me that 'some info missing'. Then, I inserted a function "if" that will leave a cell in blank for that row (=IF(S4="ok";"go ";" "), and I am trying to use the VBA instruction to not let the user save this file without making the neccesary corrections, but the VBA instruction seems not to recognize the " " marks as an empty cell, what can I do? thanks! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Making cells mandatory to fill in | Excel Discussion (Misc queries) | |||
| Cells to be mandatory fill have a formula in it | Excel Discussion (Misc queries) | |||
| Cell to be mandatory fill in | Excel Discussion (Misc queries) | |||
| Cell to be mandatory fill in | Excel Discussion (Misc queries) | |||
| Making cells mandatory to fill in if a previous cell contains info | Excel Programming | |||
Linear Mode
