Help with basic VBA Code

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
8
Hi Experts,
I need bit of help with my first VBA Project. I am trying to build a down payment and amortization calculator. Here is the VBA code I used
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$5" Then
Range("$B$6").Value = "Please Select"
End If

If Target.Address = "$B$5" Then
Range("$B$9").Value = "Please Select"
End If

If Target.Address = "$B$5" Then
Range("$B$13").Value = "Please Select"
End If


If Target.Address = "$B$5" Then
Range("$E$5").Value = "Please Select"
End If

If Target.Address = "$B$6" Then
Range("$B$7").Value = "Please Select"
End If
End Sub

I need help in two aspects
i) I tried to put all the cells dependant upon B5 in one range like range("B6, B9, B13,E5") but this does not work as desired. Also tried with $ sign
ii) I am trying to make cell B8 (price) unlocked if B5="Solar". The sheet will be password protected. I tried many solutions found online but neither of them worked.

Kindly help in this regard.

Regards
 

Attachments

  • vba.PNG
    vba.PNG
    21.6 KB · Views: 7
Last edited by a moderator:

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,361
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Let's start withe following changes to the code and work from there:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'
    If Target.Address = "$B$5" Then
        Range("$B$6").Value = "Please Select"
        Range("$B$9").Value = "Please Select"
        Range("$B$13").Value = "Please Select"
        Range("$E$5").Value = "Please Select"
    End If
'
    If Target.Address = "$B$6" Then
        Range("$B$7").Value = "Please Select"
    End If
End Sub

Now what questions do you have?
 

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
8
Private Sub Worksheet_Change(ByVal Target As Range) ' If Target.Address = "$B$5" Then Range("$B$6").Value = "Please Select" Range("$B$9").Value = "Please Select" Range("$B$13").Value = "Please Select" Range("$E$5").Value = "Please Select" End If ' If Target.Address = "$B$6" Then Range("$B$7").Value = "Please Select" End If End Sub
Thanks a lot for your response. It worked. Now the second part please which is
i. If B5=Solar, B8 should be unlocked in a protected sheet.

Regards
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,361
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Why is B8 locked to begin with? There is nothing private about amortization tables.
 

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
8

ADVERTISEMENT

Actually, we want minimum user input and maximum fields in the drop-down for selection. B8 is the product price that is fetched with Vlookup except solar. The price of Solar Products is not fixed and changes according to the configuration so cant be pre-defined. If we leave this field open for all products, it is highly likely that the user will input incorrect prices which will lead to incorrect loan amount calculation. I added the following code before End Sub in the code you provided but this did not work.

If Range("C6") = "Other" Then
Range("B8").Locked = True
ElseIf Range("C6") = "Solar" Then
Range("B8").Locked = False
End If

I used formula if(B5="Solar","Solar","Other") to generate contents of C6
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,383
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
i) I tried to put all the cells dependant upon B5 in one range like range("B6, B9, B13,E5") but this does not work as desired.
Not sure what you tried, but you can do that:

VBA Code:
    If Target.Address = "$B$5" Then
        Range("B6,B9,B13,E5").Value = "Please Select"
    ElseIf Target.Address = "$B$6" Then
        Range("$B$7").Value = "Please Select"
    End If
 

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
8

ADVERTISEMENT

Not sure what you tried, but you can do that:

VBA Code:
    If Target.Address = "$B$5" Then
        Range("B6,B9,B13,E5").Value = "Please Select"
    ElseIf Target.Address = "$B$6" Then
        Range("$B$7").Value = "Please Select"
    End If
Hi, Thanks for your response. This matter is solved and now I am trying to figure out how to conditionally unlock B8 based on the value in B5. I have explained it above. I hope you can help in that as well.

Regards
Azhar Nadeem
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,383
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Code:
    If Target.Address = "$B$5" Then
        Range("B6,B9,B13,E5").Value = "Please Select"
        If Target.value = "Solar" and Range("B8").Locked then
            me.unprotect "password here"
            Range("B8").Locked = False
            me.protect password:="password here"
         end if
    ElseIf Target.Address = "$B$6" Then
        Range("$B$7").Value = "Please Select"
    End If

for example.
 

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
8
If Target.Address = "$B$5" Then Range("B6,B9,B13,E5").Value = "Please Select" If Target.value = "Solar" and Range("B8").Locked then me.unprotect "password here" Range("B8").Locked = False me.protect password:="password here" end if ElseIf Target.Address = "$B$6" Then Range("$B$7").Value = "Please Select" End If
Thanks, RoryA. Almost there. The code unlocks B8 but the exiting formula in B8 (lookup to fetch product prices) is replaced with custom input. Actually, I want the user to put in a number for Solar only. In other cases, Vlookup should work as it is working now.

Thanks for helping out
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,383
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Not sure what you mean. That code doesn't replace anything in B8.
 

Forum statistics

Threads
1,141,871
Messages
5,709,097
Members
421,614
Latest member
RAB29

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
Top