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
Where MyMacros is the name of the procedure you want executed.
peyma replied on 14-Sep-07 12:06 PM
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
scrolling down not the button selection.the codes are like this:
' 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
as you can see after pushing the command box I can have only scroll down not
selecting button option.
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...
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.
.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
With Sheets("Sheet1").OptionButton1 'code name for the button
.Value = Not .Value
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...
dim wks as worksheet
Sheets("Sheet1") 'Add a dot no list
set wks = Sheets("Sheet1")
wks 'Add a dot get list
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...
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!!