Autofill cell based on other cell data

S

sztom

New Member
Joined
May 30, 2010
Messages
4
<o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="City"></o:smarttagtype><o:smarttagtype namespaceuri="urn:schemas-microsoft-com:eek:ffice:smarttags" name="place"></o:smarttagtype><!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if !mso]>******** classid="clsid:38481807-CA0E-42D2-BF39-B33AF135CC4D" id=ieooui></object> <style> st1\:*{behavior:url(#ieooui) } </style> <![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Hi,
I am looking for help in creation of this code.
This is Order Tracking workbook. In this workbook I have sheet named "PartsNeeded" with multiple cells as shown below:

ColA > Date
ColB > string
ColC > string
ColD> string
ColE > string
ColF> date
ColG>string
ColH>string
ColI>string
ColJ>value
There are additional columns after column J

ColE to ColI I will have manual data entry. ColH and ColI have pull down combo box with "Yes" or "No" selection. I need a VBA code which will do the following:

1. Will automatically populate ColJ cell with Number "4" if data is present in every cell in ColE to ColI
2. When data is entered in ColE to ColI after each entry message box will ask user if they want to Save changes and Prevent changes to those cells if the answer is “Yes”.
3. If there is no data is in ColE to ColI, populate ColJ with"0".
4. If data is in ColE only, then ColF must have value and both cells will change color to “Red”, ColJ will be "1". Those two columns E and F are considered as a set.
5. If Data is present in ColE and ColF and ColG, those cells to change color to “<st1:city w:st="on"><st1:place w:st="on">Orange</st1:place></st1:city>” and ColJ need "2".
6. If data is present in ColE, ColF, ColG and ColH, all 4 cells change color to “Yellow” and ColJ to "3" .
7. If ColJ value is "4", then the whole row will be “Green” and automatically be moved to sheet "OrderComplete”
<o:p> </o:p>
I have seen some pieces of code here and there, but I was not able to kludge it together correctly. I would like to have this as part of Workbook Change. <o:p></o:p>
Any help greatly appreciated.<o:p></o:p>
Thanks in advance.
sztom
<o:p></o:p>
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
You should not put this in workbook_change but in the module for the sheet PartsNeeded.
Else all these macros willrun as well when you move the completed line to OrdersComplete for example.
Always restrict the scope of your macros to where it is needed.

OK now for your sheet.

The following code should be entered in the sheet module for PartsNeeded
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:I")) Is Nothing Then
        Dim result As VbMsgBoxResult
        
        result = MsgBox("Save Changes?", vbOKCancel, "Sheet Changed")
        If result = vbOK Then
            Target.Locked = True
        End If
        If Not Intersect(Target, Range("E:E")) Is Nothing And Target.Value <> vbNullString Then
            Range("J" & Target.Row).Formula = "=CountA(F" & Target.Row & ":I" & Target.Row & ")"
            If Target.Offset(0, 1).Value = vbNullString _
               And ActiveCell.Address <> Target.Offset(0, 1).Address Then
               'Entry in Cell Ex, check to see if Fx is empty and not cursor now in Fx
               Target.Offset(0, 1).Select
               MsgBox "Entry required in F" & Target.Row & "!"
               Exit Sub
            End If
        End If
        If Not Intersect(Target, Range("F:F")) Is Nothing Then
            If Target.Offset(0, -1).Value <> vbNullString And Target.Value = vbNullString Then
               'Entry in Cell Ex, check to see if Fx is empty
               Target.Select
               MsgBox "Entry required in F" & Target.Row & "!"
               Exit Sub
            End If
        End If
        If Range("G" & Target.Row).Value = vbNullString And Range("H" & Target.Row).Value = vbNullString _
            And Range("I" & Target.Row).Value = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Gx:Ix are empty, colour Ex &Fx red
            Range("E" & Target.Row & ":F" & Target.Row).Interior.ColorIndex = 3
        ElseIf Range("H" & Target.Row).Value = vbNullString And Range("I" & Target.Row).Value = vbNullString _
            And Range("E" & Target.Row).Value <> vbNullString Then
            'Hx:Ix are empty, colour Ex &Fx & Gx orange
            Range("E" & Target.Row & ":G" & Target.Row).Interior.ColorIndex = 45
        ElseIf Range("I" & Target.Row) = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Ix is empty, colour Ex : Hx yellow
            Range("E" & Target.Row & ":H" & Target.Row).Interior.ColorIndex = 6
        ElseIf Range("E" & Target.Row).Value <> vbNullString Then
            ' all cells filled
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = 4
            MsgBox "The order is completed." & vbCrLf & _
                "The order data will be moved to 'OrderComplete'"
            Application.EnableEvents = False
            Target.EntireRow.Copy
            Worksheets("OrderComplete").Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
            Target.EntireRow.Delete
            Application.EnableEvents = True
        Else    'no data
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = xlColorIndexNone
        End If
    End If
End Sub
 
Upvote 0
Forgot to say

You need to set the columns A:I to unlocked (Format/Cells/Protection)
then protect the worksheet (Tools/Protection)

And I didn't test the code with a protected sheet. It needs protecting & unprotecting inbetween
Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:I")) Is Nothing Then
        Dim result As VbMsgBoxResult
        
        ActiveSheet.Unprotect Password:=""
        result = MsgBox("Save Changes?", vbOKCancel, "Sheet Changed")
        If result = vbOK Then
            Target.Locked = True
        End If
        If Not Intersect(Target, Range("E:E")) Is Nothing And Target.Value <> vbNullString Then
            Range("J" & Target.Row).Formula = "=CountA(F" & Target.Row & ":I" & Target.Row & ")"
            If Target.Offset(0, 1).Value = vbNullString _
               And ActiveCell.Address <> Target.Offset(0, 1).Address Then
               'Entry in Cell Ex, check to see if Fx is empty and not cursor now in Fx
               Target.Offset(0, 1).Select
               MsgBox "Entry required in F" & Target.Row & "!"
               ActiveSheet.Protect Password:=""
               Exit Sub
            End If
        End If
        If Not Intersect(Target, Range("F:F")) Is Nothing Then
            If Target.Offset(0, -1).Value <> vbNullString And Target.Value = vbNullString Then
               'Entry in Cell Ex, check to see if Fx is empty
               Target.Select
               MsgBox "Entry required in F" & Target.Row & "!"
               ActiveSheet.Protect Password:=""
               Exit Sub
            End If
        End If
        If Range("G" & Target.Row).Value = vbNullString And Range("H" & Target.Row).Value = vbNullString _
            And Range("I" & Target.Row).Value = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Gx:Ix are empty, colour Ex &Fx red
            Range("E" & Target.Row & ":F" & Target.Row).Interior.ColorIndex = 3
        ElseIf Range("H" & Target.Row).Value = vbNullString And Range("I" & Target.Row).Value = vbNullString _
            And Range("E" & Target.Row).Value <> vbNullString Then
            'Hx:Ix are empty, colour Ex &Fx & Gx orange
            Range("E" & Target.Row & ":G" & Target.Row).Interior.ColorIndex = 45
        ElseIf Range("I" & Target.Row) = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Ix is empty, colour Ex : Hx yellow
            Range("E" & Target.Row & ":H" & Target.Row).Interior.ColorIndex = 6
        ElseIf Range("E" & Target.Row).Value <> vbNullString Then
            ' all cells filled
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = 4
            MsgBox "The order is completed." & vbCrLf & _
                "The order data will be moved to 'OrderComplete'"
            Application.EnableEvents = False
            Target.EntireRow.Copy
            Worksheets("OrderComplete").Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
            Target.EntireRow.Delete
            Application.EnableEvents = True
        Else    'no data
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = xlColorIndexNone
        End If
        ActiveSheet.Protect Password:=""
    End If
End Sub

at the moment set with no password. If you want to set a password, you need to add it in the code
 
Upvote 0
Thanks sijpie for quick reply.
I must be doing something wrong, I seen the code partially working once, and no more.
This what happened. Column J value changed with every data entry in colE to I. When 4 was reached in column J the cells changed to green as they should, however during data entry the colors did not changed.
When colJ changed to 4, message box opened, per code, awaiting additional entry.
This is not necessary. Self closing Info box that this line has been moved would be better.
The cell color, did not changed if cell data was deleted, leaving cell empty.

In original post I made few mistakes, please forgive me. Here is clarification to it. #1 User Form will be used to enter data to PartsNeeded worksheet to next available row in sheet. There will be always few of them. #2 I forgot to mention that data entry must start with row 4 due to reserved first 3 rows, where row 3 has the column names.
I am currently working on the user input form. I have created short sample file, but I could not attach it. Hope this make sense.

<table x:str="" style="border-collapse: collapse; width: 695pt;" width="928" border="0" cellpadding="0" cellspacing="0"><col style="width: 77pt;" width="103"> <col style="width: 59pt;" width="78"> <col style="width: 87pt;" width="116"> <col style="width: 48pt;" width="64"> <col style="width: 47pt;" width="63"> <col style="width: 74pt;" width="99"> <col style="width: 77pt;" width="103"> <col style="width: 62pt;" width="83" span="2"> <col style="width: 54pt;" width="72"> <col style="width: 48pt;" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 77pt;" width="103" height="17">
</td> <td style="width: 59pt;" width="78">
</td> <td style="width: 87pt;" width="116">
</td> <td style="width: 48pt;" width="64">
</td> <td class="xl27" style="width: 47pt;" width="63">
</td> <td class="xl27" style="width: 74pt;" width="99">
</td> <td class="xl27" style="width: 77pt;" width="103">
</td> <td class="xl27" style="width: 62pt;" width="83">
</td> <td class="xl27" style="width: 62pt;" width="83">
</td> <td class="xl27" style="width: 54pt;" width="72">
</td> <td style="width: 48pt;" width="64">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 38.25pt;" height="51"> <td class="xl39" style="height: 38.25pt; width: 77pt;" width="103" height="51">Reques Placed</td> <td class="xl39" style="width: 59pt;" width="78"> P/N</td> <td class="xl39" style="width: 87pt;" width="116">Description</td> <td class="xl39" style="width: 48pt;" width="64">Buyer</td> <td class="xl39" style="width: 47pt;" width="63">PO</td> <td class="xl39" style="width: 74pt;" width="99">Est. PO Deliv. Date</td> <td class="xl39" style="width: 77pt;" width="103">Parts Received (Reciving)</td> <td class="xl39" style="width: 62pt;" width="83">Deli. to Production</td> <td class="xl39" style="width: 62pt;" width="83">Get data Executed (hidded)</td> <td class="xl39" style="width: 54pt;" x:str="Complete " width="72">Complete </td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl40" style="height: 12.75pt;" x:num="40285" height="17">4/17/2010</td> <td class="xl41" x:num="">777777</td> <td class="xl41">test0</td> <td class="xl41">pete</td> <td class="xl41">
</td> <td class="xl41">
</td> <td class="xl41">
</td> <td class="xl41">
</td> <td class="xl41">
</td> <td class="xl42" x:num="">0</td> <td class="xl43">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl28" style="height: 12.75pt;" x:num="40309" height="17">5/11/2010</td> <td class="xl29" x:num="">12345678</td> <td class="xl29">test1</td> <td class="xl29">Tim</td> <td class="xl29">555u(g)</td> <td class="xl28">5/11/2010 (g)</td> <td class="xl28">5/15/2010(g)</td> <td class="xl28">5/18/2010(g)</td> <td class="xl28"> x(g)</td> <td class="xl30">4(g)</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="40310" height="17">5/12/2010</td> <td class="xl26" x:num="">456789</td> <td class="xl26">test2</td> <td class="xl26">John</td> <td class="xl31">555u ('r)</td> <td class="xl25" x:num="40309">5/11/2010</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26"> x</td> <td class="xl36">1('r)</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="40316" height="17">5/18/2010</td> <td class="xl26">125hjk</td> <td class="xl26">test3</td> <td class="xl26">Mary</td> <td class="xl32">555u (o)</td> <td class="xl33">5/11/2010 (o)</td> <td class="xl25" x:num="40317">
</td> <td class="xl26">
</td> <td class="xl26"> x</td> <td class="xl37">2(o)</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl25" style="height: 12.75pt;" x:num="40313" height="17">5/15/2010</td> <td class="xl26">77PO4444</td> <td class="xl26">test4</td> <td class="xl26">Mary</td> <td class="xl34">555u(y)</td> <td class="xl35">5/11/2010(y)</td> <td class="xl35">5/19/2010(y)</td> <td class="xl25" x:num="40315">
</td> <td class="xl25"> x</td> <td class="xl38">3(y)</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24" colspan="5" style="">Multiple p/n can be added in the next free row, starting with row4 colB.</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24" colspan="8" style="">The cells in corresponding col A, C and D will get populated from database, via code executed from Getdata button on this page.</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24" colspan="8" style="">Automatically if ColJ data changes to 4,col E:J become green and whole row to be cut and pasted to "OrderComplete"</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24" colspan="5" style="">More data will get input from User Entry form in col K and Up</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl24" style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl24">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl26">
</td> <td class="xl27">
</td> <td>
</td> </tr> </tbody></table>Colors are in parenthesis for colF to J

Thanks,
 
Upvote 0
I don't know. On my little test sheet the colours work fine. As soo as i press the tab key, enter key or an arrow, the colours change in the cells.
As for the row number,
I forgot to mention that data entry must start with row 4 due to reserved first 3 rows, where row 3 has the column names.
Click to expand...
that is irrelevant as the code works on the row that is being changed. So if the headings are there, they won't be changed and so theyre is no issue. (ie I don't understand why you mention this)
 
Upvote 0
Thats correct, code worked beautifully, thanks again, if you enter data in right order. However, I am trying to make it goof-proof.
I only mentioned header row for info purpose.

What happened to me is that if , data is not entered in exact order I (auto) then E to H. Example: If user skips field F, after populating E, message box will open asking to enter data in column F, however, user skips this field and enter data in column G, the columns E, F and G change color to Orange, If user skips column H and enters data in column I, and acknowledge change, whole row regardless of value will move to OrderComplete sheet. Same apply if data is in columns E and I only or in columns E, H and I.
Is there way to prevent entry in other cells if data is not present in right order?
Order of execution. I- gets populated automatically first after the "GetData" button is executed to populate cells A, B, C, D. No manual user entry in this cell. No message to save data is required at this cell.
Then, E, F, G, H .
Also is there way if accidentally data was entered in wrong cell, to delete/undo it including revering back to previous color? At this time the only way to finish row is to fill rest of the cells.

If there is a easy way to prohibit wrong entry in cells E to H, then this would solve both issues.

Could you please help?
 
Last edited:
Upvote 0
There are two ways to fix the order or ensure that all the correct data gets entered.

1. Lock the cells in F,G & H until the previous cell hes been completed. Easy to do.

2. Create a proper pop-up userform where the user can enter and edit the data. On pressing the OK button the data gets entered into the sheet. If all textboxes have been filled the OK button changes into a Complete button. This is the neatest solution, but will require a bit more work.

For option 1 here is the code (one line added in line 9):
(I'm travelling so insufficient time to test it)
You will need to start off the sheet with columns A:E unlocked, and F:J locked.

Code: 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("E:I")) Is Nothing Then
        Dim result As VbMsgBoxResult
        
        ActiveSheet.Unprotect Password:=""
        result = MsgBox("Save Changes?", vbOKCancel, "Sheet Changed")
        If result = vbOK Then
            Target.Locked = True
            Target.Offset(0, 1).locked = false
        End If
        If Not Intersect(Target, Range("E:E")) Is Nothing And Target.Value <> vbNullString Then
            Range("J" & Target.Row).Formula = "=CountA(F" & Target.Row & ":I" & Target.Row & ")"
            If Target.Offset(0, 1).Value = vbNullString _
               And ActiveCell.Address <> Target.Offset(0, 1).Address Then
               'Entry in Cell Ex, check to see if Fx is empty and not cursor now in Fx
               Target.Offset(0, 1).Select
               MsgBox "Entry required in F" & Target.Row & "!"
               ActiveSheet.Protect Password:=""
               Exit Sub
            End If
        End If
        If Not Intersect(Target, Range("F:F")) Is Nothing Then
            If Target.Offset(0, -1).Value <> vbNullString And Target.Value = vbNullString Then
               'Entry in Cell Ex, check to see if Fx is empty
               Target.Select
               MsgBox "Entry required in F" & Target.Row & "!"
               ActiveSheet.Protect Password:=""
               Exit Sub
            End If
        End If
        If Range("G" & Target.Row).Value = vbNullString And Range("H" & Target.Row).Value = vbNullString _
            And Range("I" & Target.Row).Value = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Gx:Ix are empty, colour Ex &Fx red
            Range("E" & Target.Row & ":F" & Target.Row).Interior.ColorIndex = 3
        ElseIf Range("H" & Target.Row).Value = vbNullString And Range("I" & Target.Row).Value = vbNullString _
            And Range("E" & Target.Row).Value <> vbNullString Then
            'Hx:Ix are empty, colour Ex &Fx & Gx orange
            Range("E" & Target.Row & ":G" & Target.Row).Interior.ColorIndex = 45
        ElseIf Range("I" & Target.Row) = vbNullString And Range("E" & Target.Row).Value <> vbNullString Then
            'Ix is empty, colour Ex : Hx yellow
            Range("E" & Target.Row & ":H" & Target.Row).Interior.ColorIndex = 6
        ElseIf Range("E" & Target.Row).Value <> vbNullString Then
            ' all cells filled
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = 4
            MsgBox "The order is completed." & vbCrLf & _
                "The order data will be moved to 'OrderComplete'"
            Application.EnableEvents = False
            Target.EntireRow.Copy
            Worksheets("OrderComplete").Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial
            Target.EntireRow.Delete
            Application.EnableEvents = True
        Else    'no data
            Range("E" & Target.Row & ":I" & Target.Row).Interior.ColorIndex = xlColorIndexNone
        End If
        ActiveSheet.Protect Password:=""
    End If
End Sub
 
Upvote 0
This works very nice, even if the file is closed, when reopened next time it remembers last entry in row. One Problem I got is that if you double-click on cell, then click an any other cell the save changes message comes up and will save blank entry(The column J however will stay at 1 or 2 depending on which cell had the blank saved).
This prevents any future changes to row, making it impossible to close.
At the time of data entry, message box Save "OK" / "Cancel" option works, but if the Save is executed and blank is saved how we can go back to "bad cell(s)" and make correction?
Is there way to ignore blank or any illegal entry e.g. space, punctuation etc. ?
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
S
  • Question
VBA search tool to return data and cell color
2
Replies
15
Views
2K
StillUnderstanding
S
S
  • Question
How to get VBA search to return the row of data if the last cell in the range has no data in it
Replies
1
Views
715
Saurabhj
Saurabhj
M
  • Question
Finding a group of cells, copying data from those rows onto another sheet...
2 3 4
Replies
30
Views
2K
mlarson
M
M
  • Question
help with conditional formatting.
2
Replies
13
Views
1K
matelot2
M

Forum statistics

Threads
1,214,940
Messages
6,122,361
Members
449,080
Latest member
Armadillos

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top