just a little help please?

[jlutz]

Hi everyone,
I'm pretty sure that someone will have the formula I'm needing rather quickly for this.
Quite simply, I have values (20, 30, 35, 40, 50). I want to click on one of the cells and have it displayed in the bottom left cell. This becomes the % factor for using in the bottom right cell. I have done the drop-down validation but was wanting something slightly faster than the drop-down function. I trust that explanation makes sense and thanks in advance for the assistance! Jerry

<TABLE style="WIDTH: 303pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=403><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" span=2 width=93><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #f0f0f0" class=xl64 height=21 width=64>0.2</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 67pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl65 width=89>0.3</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=64>0.35</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl66 width=93>0.4</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 70pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl67 width=93>0.5</TD></TR><TR style="HEIGHT: 18pt" height=24><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; HEIGHT: 18pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=24>.</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: black 1pt solid" class=xl69 colSpan=2> $ - </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0"></TD></TR></TBODY></TABLE>

 

[rs2k]

Hi Jerry,

Would this work for you?

Create 5 buttons from the 'Control Toolbox' toolbar and add the code for each button;

Private Sub CommandButton1_Click()
Range("A10").Value = 0.2
End Sub

Private Sub CommandButton2_Click()
Range("A10").Value = 0.3
End Sub

Private Sub CommandButton3_Click()
Range("A10").Value = 0.35
End Sub

Private Sub CommandButton4_Click()
Range("A10").Value = 0.4
End Sub

Private Sub CommandButton5_Click()
Range("A10").Value = 0.5
End Sub

I used cell 'A10' as your 'bottom left cell' in this example and each button had its caption property set as '20%, 30%, 35% etc...'

If it works, great! if your not sure sure about vba then post back and we should be able to help.

HTH
Colin

 

[jlutz]

Colin,
thanks for the help. VBA is something I have never used before. Assistance will be needed!

 

[rs2k]

OK,
Go to 'View>Toolbars>Control Toolbox' click on the button icon on the toolbar and draw your button on your worksheet. Click on the properties icon (little yellow hand holding a piece of paper) this will display the proerties window. Change the '(Name)' property from 'CommandButton1' to 'cmd20' (Note: you dont really need to do this, but it helps when you are going through code to identify which piece of code does what) Change the 'Caption' property' to '20%' (You will notice the writing on the button change to '20%') then right click your button>View Code, the VB editor will open with a window that shows something like;

Private Sub cmd20_Click()

End Sub

Paste this bit of code in;

Range("A10").Value = 0.2

You should end up with this;

Private Sub cmd20_Click()
Range("A10").Value = 0.2
End Sub

Remember to change ("A10") to the cell where you want the value to go.

Go back to your worksheet and close the properties window. And then (VERY IMPORTANT) click the top left icon (Pencil, rule, triangle-thingy) 'Exit Design Mode' on the 'Control Toolbar' otherwise the button will remain in edit mode and wont do anything!

You can close the toolbar now, and try it out.

Click your new button and '0.2' will appear in 'A10' (or the cell of your chooseing)

Repeat for the other buttons.

Any prob's post back,

HTH
Colin