Excel - macro for button option

Asked By peyma on 14-Sep-07 11:32 AM
I have a command box on my worksheet and I wanna record a macro like
scrolling down a file and then choosing a  button option for the command
box.I do the same after selecting "record new macro" but only scrolling down
is saved in the macro!!and the button is not chosen?
any help in this regard?

James_Thomlinso replied on 14-Sep-07 11:54 AM
That button must run a macro so some sort. Just add the line

Call MyMacro

Where MyMacros is the name of the procedure you want executed.

Jim Thomlinson
peyma replied on 14-Sep-07 12:06 PM
hi Jim,
I think either you didn't get what I'm gonna do or I haven't undrestand what
you are talking about!!
I have a command box and a button option in my worksheet it's a form
actually.what I want to do is, when I push the command box , it is scrolled
down at the end of the worksheet and the button option be selected.
I wanna do this by recording a macro.what I get from the record is the codes
related to
scrolling down not the button selection.the codes are like this:

Sub button()
' button Macro
' Macro recorded 13/09/2007 by payman

ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 23
End Sub
as you can see after pushing the command box I can have only scroll down not
selecting button option.
thank you,
James_Thomlinso replied on 14-Sep-07 12:22 PM
What kind of button is it that you have. So far you have refered to it as a
Button, a command button, a button option and a command box?

Command button - Rectangular button
Option Button (aka radio button) - Circle which is filled in when pressed
I have no idea what a command box is. There are combo boxes and text boxes...


Jim Thomlinson
peyma replied on 14-Sep-07 12:34 PM
Sorry Jim, making you confused.I have a "Command Button" and an "Option
Button"(the radio button, circle one).I'm getting them from "control toolbox"
menu bar.I'm trying to define some codes for the command button by which I'm
able to scroll down and select the option button
James_Thomlinso replied on 14-Sep-07 01:20 PM
This should do it for you then... code name is listed in the name box in XL
when you select the option button while in design mode.
Sub button()

With ActiveWindow
.ScrollRow = 2
.ScrollRow = 3
.ScrollRow = 4
.ScrollRow = 5
.ScrollRow = 6
.ScrollRow = 7
.ScrollRow = 8
.ScrollRow = 9
.ScrollRow = 10
.ScrollRow = 11
.ScrollRow = 12
.ScrollRow = 13
.ScrollRow = 14
.ScrollRow = 15
.ScrollRow = 16
.ScrollRow = 17
.ScrollRow = 18
.ScrollRow = 19
.ScrollRow = 20
.ScrollRow = 21
.ScrollRow = 22
.ScrollRow = 23
End With
With Sheets("Sheet1").OptionButton1 'code name for the button
.Value = Not .Value
End With
End Sub


Jim Thomlinson
peyma replied on 14-Sep-07 01:48 PM
oh, that's fantastic.you're great Jim.thanks a lot.But just can you tell me
how and where i can find the commands like .value,  .ScrollRow and so on?and
where is the "name box  in XL" you mentioned?
many thanks Jim,
James_Thomlinso replied on 14-Sep-07 02:32 PM
Question 1 - .value and .??? (aka intellisence) is an automatically generated
list in VBA but it is only generated if you are using valid objects. Every
different object has it's own list of properties and methods. Try this...

sub test
dim wks as worksheet

Sheets("Sheet1") 'Add a dot no list
set wks  = Sheets("Sheet1")
wks 'Add a dot get list
end sub

Note that you did not get a list with Sheets("Sheet1") because Sheet1 could
be either a worksheet or a chart which have different properties and methods.
It works with wks becuase I have told VBA that it is a worksheet.

Question 2 - Name box - the name box is immediately to the left of the
formula bar. It displays the cell addresses and range names (you can enter
range names directly in this box). In general it displays the name of
whatever you have selected...

Jim Thomlinson
peyma replied on 14-Sep-07 02:46 PM
thanx again Jim,I will go thru it and I will aks you whenever I get stuck!!
peyma replied on 14-Sep-07 02:48 PM
thanx again Jim,I will go thru it and I will ask you whenever I get stuck!!