I know there's some people here that know VBA.

[Minime80]

It's probably really simple, but I can't figure it out. I'm in Excel and I want to change the value of a specific cell when an option button is selected. There are 3 buttons related to the same cell. I want the first button to change the cell to .85. The second to change that same cell to 1, and the third to 1.15. Also, this needs to be put into a separate module from the spreadsheet since the same option buttons and actions are wanted for a big group of spreadsheets. I've already got a module with some other stuff in it. All I really need to know is how to work with the damned option buttons, and maybe how to make sure it works with all the other files too.

[Marcel2008]

This is a linux forum, try microsoft forums

[Minime80]

I know this is a linux forum, and I use linux at home all the time. I'm just doing some stuff for work and need to use VBA for it and I know from reading other posts here before that there are people here that deal with VB and VBA at work. I also know that the microsoft forums suck and the people here are infinitely more helpful and polite... usually.

[jscott]

WorkSheets("index").Range(cellX, cellY).Value = someValue


Worksheets index can take a number or name.

[Minime80]

Okay, so how would I use that with the buttons?

[pointreyes]

I think you store the module as an .xla and have it as part of the add-ins. The buttons can then call the function for the value. However, I think the button events have to be inclusive to the workbook. Sorry, but I'm much more familiar with Access VBA than the other types of Office VBA syntax.

[ 18 June 2001: Message edited by: pointreyes ]

[jscott]

**** buttons, just make a macro and assign shortcut keys. Creating a userform for such a trivial task seems a bit over-the-top. But them I guess we are talking MS ;)

Press ALT+F11 to bring up the VBA Editor in Excel. I'll also assume you created the UserForm with 3 default buttons.


Private Sub CommandButton1_Click()
WorkSheets("index").Range(cellX,cellY).Value = ".85"

End Sub

Private Sub CommandButton2_Click()
WorkSheets("index").Range(cellX,cellY).Value = "1"

End Sub


Private Sub CommandButton3_Click()
WorkSheets("index").Range(cellX,cellY).Value = "1.15"

End Sub



If this isn't what you're talking about. please provide a link to what you are working on.

[Minime80]

I'm using option(radio) buttons in grouped boxes.

[jscott]

Change the above code.

Replace:
CommandButtonx_Click()

with:
OptionButtonx_Click()

[pointreyes]

Originally posted by jscott:
<STRONG>Change the above code.

Replace:
CommandButtonx_Click()

with:
OptionButtonx_Click()</STRONG>

Why use three sub routines when you can use one? Since you have the option buttons in a group (a.k.a. 'frame'), do a simple value check of the group with one sub routine.


Public Sub fraOption_Click()
Select Case Me.fraOption.Value
Case 1 'OptionButton1
WorkSheets("index").Range(cellX,cellY).Value = ".85"
Case 2 'OptionButton2
WorkSheets("index").Range(cellX,cellY).Value = "1"
Case 3
WorkSheets("index").Range(cellX,cellY).Value = "1.15"
End Select
End Sub

[jscott]

Well, I figured if they didn't know about _Click(), chances are "Objects",looping,switches might also be out of their league. ;)

[YaRness]

Originally posted by Marcel2008:
<STRONG>This is a linux forum, try microsoft forums</STRONG>
this is a programming forum. it says in the subject it's about VBA (which some of us have to program in every now and then even if we don't want to.). if you don't have something useful to contribute, why bother posting.

and you can search the archives, posting non-linux stuff is not unheard of here.

Minime80: if no one here can help you, or help you enough, i HAVE used a site that has some forums about MS stuff, and gotten decent responses. i think it's a lot of Access stuff, but I think they had forums for other office products as well. the url is http://www.athree.com/cgi-bin/discussion/wwwthreads.pl

i always like to ask here first, because most people here are pretty qool.

[Minime80]

Okay, I tried the one example with the select case in it and it doesn't like the Me keyword for some reason. It just keeps saying "invalid use of the Me keyword"

[bolty]

The most efficient code is

Public Sub fraOption_Click()

WorkSheets("index").Range(cellX,cellY).Value = fraOption.Value * 0.15 + 0.70

End Sub

Its a one liner!