• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 305
  • Last Modified:

Radio Buttons- VB Excel

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
ashblynn02
Asked:
ashblynn02
  • 5
  • 4
1 Solution
 
RosenthalDeveloperCommented:
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
 
ashblynn02Author Commented:
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
 
RosenthalDeveloperCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ashblynn02Author Commented:
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
 
RosenthalDeveloperCommented:
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
 
ashblynn02Author Commented:
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
 
RosenthalDeveloperCommented:
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
 
ashblynn02Author Commented:
Yes it did!! Thank you so so so much! Your were a tremendous help.
Thanks again
0
 
ashblynn02Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now