How can I allow a user to change a cell in excel only under particular conditions

0 votes
In an excel document, column A's whole cells are filled with numerical values, and column B has additional numbers.

I want to develop a VBA script (or other solutions, if there are any) to manage to change the values in column A.

To be clear, cell A1 can only be changed by the user of Excel if cell B1's value is zero. The same holds true for the succeeding rows; hence, row A2 can only be modified if row B2 is equal to zero, and so forth.

I have searched on the internet but I can't find a good solution, in particular, because this must work on a large number of cells.
Jan 31, 2023 in Others by Kithuzzz
• 38,000 points
604 views

1 answer to this question.

0 votes

Copy the next event code in the respective sheet code module, and play with changing:

Option Explicit

Private newVal
Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.cells.count > 1 Then Exit Sub
        If Target.column = 1 Then
            If Target.Offset(, 1).Value <> 0 Then
                Application.EnableEvents = False 'to avoind triggering again the Change event
                  newVal = Target.Value
                  Application.Undo
                Application.EnableEvents = True
            End If
        End If
End Sub
answered Jan 31, 2023 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,600 points
2,353 views
0 votes
1 answer

Is there a way to test a formula result in excel and type it only once, all within one cell and not using a user defined function?

Use the Let function: =LET(Value,A1+B2+C4+G3+B4,IF(Value>10,"No",Value)) I hope this helps ...READ MORE

answered Jan 9, 2023 in Others by narikkadan
• 63,600 points
674 views
0 votes
0 answers

how can I deploy a test in pytest framework to airflow using DAG?

I have established an automative testing framework ...READ MORE

Oct 4, 2021 in Others by Yuan
• 120 points
673 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 63,600 points
3,747 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,242 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,688 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
949 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
1,081 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 63,600 points
793 views
0 votes
1 answer

How can I format bytes a cell in Excel as KB, MB, GB etc?

In Excel's formatting features, computations are not ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,600 points
1,926 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP