Trying to activate cells by input in other cells

[bralew]

Hi,

I'm sure there's some capable individuals on here who can offer assistance on this basic VBA conundrum.
If I place 750 in say, cell B4 I'd like cell B7 to reveal 54. I'd like to achieve this using VBA if possible.

many thanks

[CAntosh]

a) Could you please change your post title to better reflect your issue; maybe something like "Reveal cell value based on input in another cell" so that future searchers with similar issues can find this thread?

b) Is VBA a requirement? This sounds like something that can be fixed with an IF formula...

c) Is there some math that gets you from 750 to 54, or is it just a straight - If 750, then 54 - link?

[bralew]

Hi thanks for your response.

The numbers are unrelated mathematically and I've managed the spreadsheet using the IF command in Excel. However, the complexity of the file has grown and I need a solution in VBA format.
There are multiple commands but I can adapt this one to suit the rest.

Thanks, the 750 to 54 is just a link as you've illustrated in your previous post

(No sure where I can edit the title of my post)

[CAntosh]

To change the title of the thread, click EDIT on the original post, then click the Go Advanced button, then change the title. If two or more days have passed, the EDIT button will not appear, and you need to ask a moderator to change the title.

Is there a range, or particular pattern to what gets changed, what cell it effects, and why? A worksheet change event can be written for every relevant cell, but that approach will be far less efficient than IF formulas or a selection change even covering a wide range of cells. For the operation you've specified, it would look like this:

Please Login or Register  to view this content.

You could add additional cell combos by expanding the intersect range and using ElseIf conditions, but =IF(B4=750,54,"") in B7 and the other necessary cells would be easier to maintain.

[bralew]

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("C5").Value = 750 Then
Range("C4").Value = 54
Else
Range("B8").ClearContents
End If
Application.EnableEvents = True
End Sub


Hi all,

Thanks for your input. Both examples worked brilliantly. However, I have chosen the above code because its easier for me to understand given my infancy
using VBA code.
I also require an additional instruction but I'd like it put into another Private Sub (is this a procedure?) as I can create notes and generally navigate easier.
The instruction alongside the above is to change C6 to'P' and C3 to 'Square' and C8 to unbraked.
The data validation that exists over rides the macro which is perfect as these are default instructions which can change considerably at times
but are great for starting.
I should be able to struggle on after this.

cheers all

[CAntosh]

I'm glad you found something that worked! You can safely refer to what you have as a "procedure". For your next step, I'm not sure what you mean by "alongside the above"? If you want it to only happen when C5 = 750, then you can add:

Please Login or Register  to view this content.

directly beneath your Range("C4").Value = 54 line (and above the "Else"). If you want it to happen every time there's a worksheet change, then add the same three lines beneath your "End If" line.

[bralew]

Thank you Cantosh, ill give it a try now

:-)

[bralew]

Cantosh, your a bloody genius!!!

Thanks ever so much.

I have one more conundrum and I should be able to struggle on.

I need a separate instruction to do the same as the first but this time another product. I need to select 1800 in cell C5 and this then to turn C6 to "Y", C4 to "77" and C9 to 250x40

Can you help?

[bralew]

Hi cantosh, can you tell me why the macro you sent works in the form sheet in VBa editor but not in the Module, I used to fire them from module. will it make a difference that ive put them in the sheet?

[alansidman]

@bralew
While we don't have many rules in this forum, you have apparantly violated two of them. Please take the time to read the rules you agreed to when you joined this forum and correct the issues below.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[bralew]

Thanks Cantosh,

I've changed the title of my thread and will use the necessary code marks in furure,

thanks

[alansidman]

@bralew

Please read the forum rules. Your change to your thread title is still not in compliance with the rules and please edit your current posting to correct the code tags.