?
Solved

Radio Buttons- VB Excel

Posted on 2007-10-18
9
Medium Priority
?
295 Views
Last Modified: 2016-08-29
First off, I want to thank you  for all your help!!

Second, I have asked a similar question like this one but I still can't get it right and how I want it to turn out.

I have a form object in the excel macro asking a user for a start date then from there they have a choice between 3 months, 6 months, 9 months and 12 months (this meaning if the user would chose 3 months, then 3 months would show up from the 'month' of the start date entered by the user - (i.e., if the start date was Jan 1, 2007... and 3 months was chosen- Jan, Feb, March would show up). The months (celendars) are on an excel worksheet and are 'hand' made... each box is a date (January1, January2 and so on...) and then I highlighted the whole month and named it "January, February, March- or which ever month was highlighted so that each would have its own individual name to be identified by.

So what I need to happen is when the user choses the radio buttons- I want which ever option they chose to come up on excel (i.e., if the start date was Jan 1, 2007... and 3 months was chosen- Jan, Feb, March would show up).

I have this so far but I can't figure out what is going on... I think I have too much involved in the code for just this little piece of work.

Private Sub commandbutton1_Click()
Dim i As Integer
Dim mRange As Integer
i = 1
For Each c In Me.optgroupmonths.Controls
    If c.Value Then
        mRange = 3 * i
        Exit For
    End If
    i = i + 1
Next c

If mRange = 0 Then
    MsgBox "Please Select a month range 3, 6, 9 or 12"
Else
    Application.Workbooks("hg.XLSM").Worksheets("sheet3").Select
    -----------------------------------------------------------------------------THIS IS WHERE I DON'T KNOW HOW TO ACTUALLY IDENTIFY THE X MONTHS CHOSEN....
End If
End Sub

I also want to apologize if this is confusing. I am trying to explain it to the best of my ability...


Thanks again!
0
Comment
Question by:ashblynn02
  • 5
  • 4
9 Comments
 
LVL 1

Accepted Solution

by:
Rosenthal earned 2000 total points
ID: 20103585
Well.
This routine puts in the mRange variable (at the point you felt yourself lost) the value of the option button chosen by the user (i.e. 3, 6, 9 or 12).
The use of this information depends on your worksheet layout.
If each column of the worksheet contains a month, just use mRange as an offset.
Let's say the user choose the first optionbutton (so mRange=3) and your month data area starts on B4 (January) you should access like this.
Range("B4").offset(0, mRange) will be march
Range("B4").offset(0, mRange -1) will be february and
Range("B4").offset(0, mRange - 2) will be january.
That will give you access to the first cell of each month (if they are in columns).        
0
 

Author Comment

by:ashblynn02
ID: 20108058
that is 95% of what I am looking for but how do I handle it if the user is entering in the date and that is the "month" I have to go off of? So if the User would insert the Date as February 4, 2007... how do I have then have the months start with Februrary then go tp March and April (if the 3 option button is chosen)

I uploaded my information if you think it would be easier to look at it that way....
View all files for Question ID: 22902423
https://filedb.experts-exchange.com/incoming/ee-stuff/5094-Calendar.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/5084-hg.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/5310-10202007-.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/5084-hg.zip 

thanks again for your help!
0
 
LVL 1

Expert Comment

by:Rosenthal
ID: 20108547
Hi.
I need to know what do you mean by "show up".
The selected (initial date) month calendar should be on the top of the screen?
First cell selected?
Only the selected months are to be shown (hide all others)?
Is it possible to change a little your worksheet to make VBA code easier?

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ashblynn02
ID: 20108640
Sorry about the confusion but what I am trying to say is, when the 3, 6, 9 or 12 month option is chosen, The months that are not chosen will "hide" or go away for the time being. If the start date was June 16  and if option '3 months' was chosen then June, July and August would be available on the excel spreadsheet to look at and all other months would be hidden.

and the "intial date" selected would be entered by the user on the 'user form' and then that is when the option buttons are also used. I guess one part I am confused  about in  the code is how to do I place the start date within the option buttons so it knows where to begin??

and yes.. it is def. possbile to change a little (or a lot) lol of my worksheet to make is easier. Easier is always better. I am just *new* at this and am learning as I go, so I think I take things the harder route bc i really don't know the difference lol!!

Thanks again for all your help!
0
 
LVL 1

Expert Comment

by:Rosenthal
ID: 20111944
Hi.
I think we are getting there.
I worked a solution and uploaded it for you.

View all files for Question ID: 22902423
https://filedb.experts-exchange.com/incoming/ee-stuff/5094-Calendar.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/5084-hg.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/5310-10202007-.zip
 
Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/5094-Calendar.zip 

Tell me if you have any problem to find or download it.
I am learnnig how to use EE interface and I am not sure I did it right.
Best regards,
Rosenthal.
0
 

Author Comment

by:ashblynn02
ID: 20122660
That is excactly what I want, Thank you SO SO much! but I have one little problem I believe! It is saying there is an error at this part of the code :: InitialCalendarYear = ThisWorkbook.Sheets("Month Calendar").Range("H2") :: I tried renaming it and then replacing the name, but that didn't work. I don't know exactly how to fix this.

I do want to thank you for all your help. it is greatly appreciated!!
0
 
LVL 1

Expert Comment

by:Rosenthal
ID: 20122997
Hi.
This error probably occurs because the name of the worksheet you are using is not "Month Calendar".
Try this :
Dim InitialCalendarYear as integer
InitialCalendarYear = ThisWorkbook.Sheets("Sheet3").Range("H2")

There are only 2 definitions on this command: InitialCalendarYear is a integer variable and the name of the worksheet where the calendar is (in my example "Month Calendar").
The cell H2 should contain the initial year (ie 2007) and must be in sheet named in the command.
Hope this will solve it for you.
Best regards,
Rosenthal.
0
 

Author Comment

by:ashblynn02
ID: 20123007
Yes it did!! Thank you so so so much! Your were a tremendous help.
Thanks again
0
 

Author Comment

by:ashblynn02
ID: 20193528
I'm sorry to open this question back up but I am having a problem with it working again. I added some columns to the calendars and stuff and now it wont work at all. I tried to mess around with it to change some code numbers and it didn't work. Is there any way you can help me. I will post my project again if that would help. Thank you so much!


View all files for Question ID: 22902423
https://filedb.experts-exchange.com/incoming/ee-stuff/5094-Calendar.ziphttps://filedb.experts-exchange.com/incoming/ee-stuff/5084-hg.zip
https://filedb.experts-exchange.com/incoming/ee-stuff/5310-10202007-.zip
 

Direct link to your file
https://filedb.experts-exchange.com/incoming/ee-stuff/5310-10202007-.zip 
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question