Command buttons

[RoboRaz]

Hello,

I'm new here and I think this is a fantastic community. I need some help here since I'm very new to excel's developer option.

I would like to know how I could use a command button to address a cell value into a Text box.

I want the user to click on a button and update values in multiple text boxes from different cell values and also update charts along with it so I'd like to know what code will go behind it.

Thanks!

 

[RoboRaz]

Re: Need help with command buttons

Can anyone please help me

 

[My Aswer Is This]

Re: Need help with command buttons

Well this would work for one textbox.
But when you say multiple textboxes we would have to know what textboxes
and from what cells.

And just saying update charts is way to vague a question.

Private Sub CommandButton1_Click()
TextBox1.Value = Cells(1, 1).Value
End Sub

We always need exact details.

 

[RoboRaz]

Re: Need help with command buttons

Well this would work for one textbox.
But when you say multiple textboxes we would have to know what textboxes
and from what cells.

And just saying update charts is way to vague a question.

Private Sub CommandButton1_Click()
TextBox1.Value = Cells(1, 1).Value
End Sub

We always need exact details.

Thanks for the reply really appreciate it. I am getting a Run Time Error '424' while applying this. I would like to apologize I am very new to Excel's developer option but I look forward to learn.

I have organized my data according to certain categories for which I have separate buttons. So, if I press the button for 'Ketchup category,' I get the total advertising spend of Ketchup category in TextBox1, the total GRPs in TextBox2 and etc.

I have 'four' such Text Boxes and I have a total of 7 categories i.e. 7 command buttons.

I have made tables for my raw data and what I want is for say....Total of Spend in Ketchup category to show in TextBox 1 if I hit the Ketchup button and Total spend number of Salt category if I press the Salt button.

Just like that I have four more charts, one Bar chart, 2 Doughnut charts and one line graph which will be linked to the data in tables for each category. So If I press any button, all the Text Boxes and charts will update according to each category simultaneously. If you would like, I can post a screen shot.

 

[My Aswer Is This]

Re: Need help with command buttons

If your having trouble using this one little script I gave you I do not think we can proceed till you can get something like this to work

1. Do you have have a activex Textbox on your active sheet named "TextBox1" ??
2. If you do and you put my script into a activex button the the script should put what ever value you have in Range("A1") of the active sheet.

Into your TextBox named "TextBox1"

I assume your not using a "Apple" computer and I assume your workbook is Macro enabled.

So work on this more and let me know when you can get this to work.
Have you ever used Vba before?

You said this:

"I have organized my data according to certain categories for which I have separate buttons. So, if I press the button for 'Ketchup category,' I get the total advertising spend of Ketchup category in TextBox1, the total GRPs in TextBox2 and etc. "

Are you saying you already have all this working and want more help or are you saying you want this setup to do this?

Thanks for the reply really appreciate it. I am getting a Run Time Error '424' while applying this. I would like to apologize I am very new to Excel's developer option but I look forward to learn.

I have organized my data according to certain categories for which I have separate buttons. So, if I press the button for 'Ketchup category,' I get the total advertising spend of Ketchup category in TextBox1, the total GRPs in TextBox2 and etc.

I have 'four' such Text Boxes and I have a total of 7 categories i.e. 7 command buttons.

I have made tables for my raw data and what I want is for say....Total of Spend in Ketchup category to show in TextBox 1 if I hit the Ketchup button and Total spend number of Salt category if I press the Salt button.

Just like that I have four more charts, one Bar chart, 2 Doughnut charts and one line graph which will be linked to the data in tables for each category. So If I press any button, all the Text Boxes and charts will update according to each category simultaneously. If you would like, I can post a screen shot.

 

[RoboRaz]

Re: Need help with command buttons

 

[RoboRaz]

Re: Need help with command buttons

If your having trouble using this one little script I gave you I do not think we can proceed till you can get something like this to work

1. Do you have have a activex Textbox on your active sheet named "TextBox1" ??
2. If you do and you put my script into a activex button the the script should put what ever value you have in Range("A1") of the active sheet.

Into your TextBox named "TextBox1"

I assume your not using a "Apple" computer and I assume your workbook is Macro enabled.

So work on this more and let me know when you can get this to work.
Have you ever used Vba before?

You said this:

"I have organized my data according to certain categories for which I have separate buttons. So, if I press the button for 'Ketchup category,' I get the total advertising spend of Ketchup category in TextBox1, the total GRPs in TextBox2 and etc. "

Are you saying you already have all this working and want more help or are you saying you want this setup to do this?

I've added an activeX text box now and it's working perfectly. Thanks man I'm starting to understand it a little. All the buttons are now updating the numbers in the Text Box. I need to update the charts now. Will this work on regular charts in excel?

Unfortunately no, this is my first project on vba.

For the last part, "I want this setup to do this"

 

[My Aswer Is This]

Re: Need help with command buttons

Yes you should be able to manipulate charts the same way.

Charts get all their data from cells.

Lets say a chart gets it value from Range("B1")

If you wanted you could enter a value in "Textbox3" and if you had a script like this in "Textbox3"

The value entered into "TextBox3" would now be entered into Range("B1") which would update the chart.

Private Sub TextBox3_LostFocus()
Range("B1").Value = TextBox1.Value
End Sub

This would then not require a Button and a Textbox to update a value the chart depends on.
The script runs after you enter a value in the TextBox and then exits the Textbox
Or if you Like having buttons and textboxs just do it the same way we did things in last post but have it change the cell value the chat is dependent on.


One Command Button can do more then one thing if you want like this:

Private Sub CommandButton1_Click()
Cells(1, 1).Value = TextBox1.Value
Cells(1, 3).Value = TextBox1.Value
Cells(1, 7).Value = TextBox1.Value
End Sub

 

[My Aswer Is This]

Re: Need help with command buttons

When referring to cells on your sheet you can write scripts two different ways.

Cells(1,1).value="23"

Or
Range("A1").value="23"

Cells(1,1) meaning Row (1) Column(1)

As Range("A1") means the same.


Or a range of cells like this:
Range("A1:B20").value="Cat"

Try it and see what it does.

 

[RoboRaz]

Re: Need help with command buttons

When referring to cells on your sheet you can write scripts two different ways.

Cells(1,1).value="23"

Or
Range("A1").value="23"

Cells(1,1) meaning Row (1) Column(1)

As Range("A1") means the same.


Or a range of cells like this:
Range("A1:B20").value="Cat"

Try it and see what it does.

Hello mate, thanks so much for your help it's really working wonders. I've been working on Range and it had been working fine up till now.

I used the following code for my charts:

Dim CHARTDATA As Range
Set CHARTDATA = Range(Range("AA08"), Range("AA08").Offset(0, 1).End(xlDown).Offset(-1))
ActiveSheet.ChartObjects("Chart 1").Chart.SetSourceData Source:=CHARTDATA

Set CHARTDATA = Range(Range("AA08"), Range("AF08").Offset(0, 1).End(xlDown).Offset(-1))
ActiveSheet.ChartObjects("Chart 2").Chart.SetSourceData Source:=CHARTDATA

It is picking up all the columns in between Columns "AA" and "AG" whereas I want Chart 2 to show values for Column AA (X-Axis) and AG (Y-Axis). Please help.

PS. Things are working fine on Chart 1 with this code.