userform- input frm cell and change it if required

[praveen_ce]

hi ppl,

i am created a following userform

takes value of X and gives value of Y .. simple one

and i did it


X($A$1) =1 ($b$1)

Y($a$2) =X+2 ($b$2)

'

Private Sub CommandButton1_Click()


Range("b1").Value = TextBox1.Value


TextBox2.Value = Range("d3").Value


End Sub

'


but now my requirement is , as user runs this userform

X should take watever value its there on $b$1

if user changes it to another value .. then it should give corresponding Y value


i tried doing it.. its not working


'

Private Sub TextBox1_Change()

TextBox1.Value = Range("b2").Value

End Sub


Private Sub CommandButton1_Click()


Range("b2").Value = TextBox1.Value

TextBox2.Value = Range("b3").Value


End Sub


'

please help me

 

[Hui]

Praveen_ce


Firstly, Welcome to the Chandoo.org Forums


What you require is a Worksheet_Change even to fie when the Cell B1 changes


It will be something like

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$B$1" Then Exit Sub
CommandButton1_Click
End Sub

[/pre]

Not sure exactly what your trying to use but you could possibly also use the Evaluate function.

Have a read about it here: http://chandoo.org/wp/2011/05/16/lost-excel-functions/

It will enable you to calculate a function or plot a chart of a formula without any VBA

 

[praveen_ce]

Hui thnks for helping me

i preparing a macro where i have 20 inputs ..some are repetitive so i want input 2 take default value

i dont want user to get frustrated while adding inputs

so i have put some default values in inputs and give an option to change it if doesn't like it then save time and energy ..

that's y i wrote second code but its not working


i hope i explained u properly

 

[NARAYANK991]

Hi Praveen ,


I am not clear about your requirement ; can you either explain in more detail or upload your workbook ?


From what I understand , you have users entering data into the worksheet ; since this data entry is repetitive , you would like default values to be presented to the user ; if the default values are acceptable , the user just presses the ENTER key , otherwise the value is changed to whatever is required. Once either the default value is accepted , or a new value is entered , you would like the value to be transferred from the textbox to the worksheet cell. Is this correct ?


Can you give specific details such as :


1. What are the cell addresses where the final entered values are to be stored ?


2. What are the cell addresses where the default values are available ?


Narayan

 

[praveen_ce]

@narayan : thank you for suggestion

watever you said is rite ..tats wat i wanted

i solved it by creating a click button in userform that wil extract default values frm worksheet


i have doubt

where and how do i upload worksheet in this website ?


thnk u guys

 

[SirJB7]

Hi, praveen_ce!

Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!