Help with basic VBA Code

AzharNadeem

New Member
Joined
Mar 16, 2014
Messages
9
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: 8
Last edited by a moderator:

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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?
 
Upvote 0
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
 
Upvote 0
Why is B8 locked to begin with? There is nothing private about amortization tables.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Not sure what you mean. That code doesn't replace anything in B8.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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