Just post a message in this thread.
I need the following information.
Forum Name
Forum Description (Optional)
Also if you want to be emailed when there is a new thread or new post, let me know which one and what email to send the message to.
Printable View
Just post a message in this thread.
I need the following information.
Forum Name
Forum Description (Optional)
Also if you want to be emailed when there is a new thread or new post, let me know which one and what email to send the message to.
Good news
Hi DRJ,
I hope I'm putting this in the right place. An idea for a forum if I may. I work for a company in their training department and have found that at times some people pick up things easily and others do not however. I could put a training session together and no one understands it, however if you change your approach or the information, straight away a light comes on. Could I make a suggestion of a forum where say VBA commonly used words are explained by who ever in the simplest way they can. That way some one may come up with a good way of explaining something. Such as dim arrays so that a dummy like myself might get the light to at least flicker.
Hello Peter, good idea! The only drawback I see is this information is already located in the Help files. Are you saying you want to expand such information, putting a more down-to-earth approach on it? That would be quite an undertaking.
Do you know what you would call it? Do you have an idea of the threads that would be posted? Would it be a Q&A place to ask about specific methods/properties/calls? Interesting, but I'm a little curious. :)
My two cents worth. Sometimes when people respond to questions and provide answers, those that get the help want to understand the answer but have questions about the answer like what exactly does Select Case do and how does it solve my problem? Granted, the answer is as close as your favorite search engine, but it might be nice to have an example of the most common answers to questions with an explained example. I suppose you could call it the Learning KB or something.
That brings up the question what do you cover and who has the time to do it. It is an interesting idea I will grant you but the layout and content would have to be agreed upon and find the time to do it.
Thank you both for your replies.
Firefytr I think Austenr may have hit it on the head. How some of these things do what they do. I look at alot of threads and I get the feeling some people do great to help and the solution is there. However sometimes how that solution was arrived at or even contemplated is not explained. I myself am new to the forum and to VBA. Some of the postings are by "newbies" I think the answer is great and the solution is correct however could I apply it to a similar problem. Not always. So maybe call it the basics of basics or the stepping stones of VBA.
Maybe its just me saying I'm real dumb and if some of our learned friends could explain their solutions sometimes aimed at the dummies.
How about creating a section called "Learning Forum". Those long time board answers (won't name names, might leave someone out) could drop in and post some code of solutions that are commonly asked when time allows complete with documented steps explaining what the code does and let someone approve it. Then it could be moved to an area for reference later by anyone wanting to stop by. A lot of the code in the KB is fairly complex for those starting out to comprehend. Just an idea.
What about calling the forum: Coding Food for Thought with the explanation of the content being the old adage that giving a man a fish feeds him for a day, teaching a man to fish feeds him for a lifetime.
Hello People, Is there away we can get more people to view this thread. I am sure this should have generated more intrest from the newer people. Is there a way to drop it into all The forums. With a different title and see what comes of it. Firefytr your Site Admin what do you think?
Lets try this.
I want to make a calendar on a form or work sheet. (Please don't give me the thing already made.)
I know how many days in a year and a month and I know this changes for a leap year. Lets start real simple.
If I put a range say of 31 cells how do i fill it with the dates of a month.
For x = 1 to 31
range(whatever).value = x
next x
Will fill the range with 31's
So what do I need to do to make the range 1 to 31?
Now if we were to do this as a project and build it up till we have a calendar that works and explain what we do along the way.
I'm not doing the explaing I'm asking the questions.
What we need are comments like before you try to fill the range you need to..................................
The first thing we would say would be, why re-invent the wheel. There is a calendar control that you can invoke and use that in a worksheet.
But ... do you want to know how to get the solution to the problem as stated, or how to do it the way you thought of?
This highligfhts one of the problems we all encounter, there is no 'right' way, no 'wrong' way, but lots of different ways.
Thanks for the reply XLD.
Its not so much the calendar I need. I know its there. Its the steps to making one I'm interested in. I would like to get the solution and if the way I thought of is not correct. Or in your view not correct and there are other ways to do it. Thats what I'm asking. How you would do it and why? Theres maybe a million ways to do it. I'm trying to get one I understand.
Okay, understood.
One thing that we don't know yet is what you want to do with the calendar, so I will just start by getting a date from the calendar.
I would use the calendar control. You can get it at http://www.fontstuff.com/vba/vbatut07.htm.
I would start by hiding the control, not making it visible. Then I would use the Worksheet_SelectionChange event and if the target cell is selected, make the control visible. The selected date would be returned to the target cell of the event. ARe you familiar with event coding?
Xld
Why as an add in and not a userform?
As an add in the calendar is not visible until I right click and "insert date"
As for event coding I understand some of it. However SelectionChange I did try to get it to work however I was not limited to the cell I wanted. It was when ever there was a change. So no I don't know how to relate it to a specific cell change.
Don't know which cell you want to have activate the calendar, but here is the general idea. Substitute UserForm1.Show with the calendar form's name.Show. Also change "G32" to the actual cell you would like to put the date into.
Regards,Code:Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim MyRange As Range
Set MyRange = ActiveCell
If Not Intersect(MyRange, Range("G32")) Is Nothing Then UserForm1.Show
End Sub
Thanks Brandtrock.
Please break down the
Yes this shows the userform But when I click it nothing. I presume now I need to add range("a5").value = Calendar1.value. What if I wanted to fill say E3:J8 with all the calendars values is it possible.Code:If Not Intersect(MyRange, Range("G32")) Is Nothing Then UserForm1.Show
The If not intersect & Is nothing. Please explain.
Intersect(MyRange, Range("C32") is the check to see if the Target cell which was called MyRange is in Range("C32") in this particular case. This can be expanded to any appropriate range reference as required.Quote:
Originally Posted by peterwmartin
Once this is checked, one of two states exists, either the cell WAS in the range, or it WASN'T. We want the code to execute when the cell IS in the range. In order to do this we "trick" VBA by using a double negative of sorts.
If our check is Nothing that means the Intersection is the null set and we don't want the code to fire. When we use Not in front of the check then we are asking to see if the check is Not Nothing, or Something which means our Target is in the desired range.
While our parents and grammar instructors are likely to disown us for using this double negative, it is quite useful in coding. I hope that this is understandable as I am rather sleepy right now. Just hanging out waiting to drive the Mrs. to work so I can have the car for transporting the boys to school later in the morning.
I am guessing that you have successfully used this code in your own sheet and adapted it accordingly. The form in my example was blank simply to illustrate how launching the calendar would work. Once the calendar is launched, then the code in the calendar form can be modified to populate whatever range you desire with the user's selection.Quote:
Originally Posted by peterwmartin
I haven't looked at the one you are using as I have Phil Johnson's calendar on my machine and am familiar with it. I know his has some bugs with international dates, but I don't need to worry about that in my situation. In any case, if you need help figuring out how to get the choice into a range like E3:J8, someone here will help you out. I'll check back tonight to see if you've got it handled.
Regards,
Hi Brandtrock,
Yes I am changing and trying the code in different ways.
I tried
Which did the opposite to what you gave. Every other cell would show the userform but not G32.Code:If Range("G32").Select Then UserForm1.Show
Sorry I did first duck for cover when I read your explenation.
As I understand it you are just checking to see if the activecell and G32 are the same cell. Therefore no intersection.
Is that correct or have I not understood?
Thanks
Actually, it is testing that there IS an intersection, that is the range where the target cell and the nominated cell intersect is NOT nothing.Quote:
Originally Posted by peterwmartin
I would use the calendar control directly rather than a userform as it is simpler, the form just adds another level of complexity and management required. I wasn't suggesting using the calendar add-in, just giving you somewhere to download the mscal.ocx file if you don't already have it.
This code handles a calendar simply by selecting any of cellsE3:J8.
Code:Private Sub Calendar1_Click()
ActiveCell.Value = Me.Calendar1.Value
Me.Calendar1.Visible = False
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const WS_RANGE As String = "E3:J8" '<=== change to suit
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Me.Calendar1
.Visible = True
.Left = Target.Offset(1, 1).Left
.Top = Target.Offset(1, 1).Top
End With
Else
Me.Calendar1.Visible = False
End If
End Sub
Forgot to mention, you can get the calendar control onto the worksheet by making the Control toolbox toolbar active (Tools>Customize>Toolsbars, and check Control Toolbox), then click the 'More Controls' button and pick the Calendar Control off of that and draw onto the worksheet.
thanks Xld.
I may not have explained myself clearly. What I would like to do is have say E3 display the 1st E4 Display the 2nd etc.
I have put a list into each of the cells and can change the Calendar to the Month and Year there may be an easier way to do it. However this works. Now I would like to have the other cells display the days and update if Month and year are changed.Code:If Range("f2").Value = "Jan" Then UserForm1.Calendar1.Month = (1)
If Range("f2").Value = "Feb" Then UserForm1.Calendar1.Month = (2)
If Range("f2").Value = "Mar" Then UserForm1.Calendar1.Month = (3)
If Range("f2").Value = "Apr" Then UserForm1.Calendar1.Month = (4)
If Range("f2").Value = "May" Then UserForm1.Calendar1.Month = (5)
If Range("f2").Value = "Jun" Then UserForm1.Calendar1.Month = (6)
If Range("f2").Value = "Jul" Then UserForm1.Calendar1.Month = (7)
If Range("f2").Value = "Aug" Then UserForm1.Calendar1.Month = (8)
If Range("f2").Value = "Sep" Then UserForm1.Calendar1.Month = (9)
If Range("f2").Value = "Oct" Then UserForm1.Calendar1.Month = (10)
If Range("f2").Value = "Nov" Then UserForm1.Calendar1.Month = (11)
If Range("f2").Value = "Dec" Then UserForm1.Calendar1.Month = (12)
UserForm1.Calendar1.Year = Range("h2").Value
'For x = 1 To 42
'Range("c4:i9").Value = UserForm1.Calendar1.Day(x)
'Next
End Sub
It is very good the amount of help I am getting from all of you. However I was hoping other new people would get involved. If anyone who is new is watching what is going on here please don't hesitate to jump in.
I am at a loss as to why you would need a Userform, it now seems to me that you are creating a calendar in a worksheet range?
Is this the sort of thing that you want?
Xld. Yes that is similar to what I want. As to why I need a userform I don't know. Originally I said a form or a work sheet. You suggested I get the Add in I presumed you were going to do it from that. My mistake sorry.
What I said was to use the calendar control, and pointed you at that site in case you didn't have it. I hadn't been there for a while, and had forgotten that he also has an add-in. Should have pointed you at Ron de Bruin's site, he has an example using it from a worksheet, tried to cut out the middle-man :)Quote:
Originally Posted by peterwmartin
Ok so I got a little lost. Shaken but not defeated.
Can we go back and try how would I go about filling a range of say 6 columns and 6 rows with the days of a month.
Mine's 6x8, but is this the sort of thing that you mean
Yes thank you xld.
I am trying to look through it to see how you have done it. It does not seem to have any code. So I presume it is done from the formula bar alone. If so I need to learn a lot more about excel itself.
It is all formulae. They are quite straight-forward, but just fire questions if you want any explanations.
Hi Peter
Did you see this post?
http://www.vbaexpress.com/forum/showthread.php?t=11013
Very neat BobQuote:
Originally Posted by xld
:clap::clap::clap:
You so far have sent me from vba to what you call straight forward excel formulae. I fear with this question you might send me back further. Maybe high school math.
Some of your functions in the formula bar have (TODAY())) I understand that if you open brackets you need to close them. Could you write that as (TODAY)))( or would this change the result. Also could you just close them all at the end?
It's so long since I went to school I hope that is high school math. If it is back before high school. This is going to hurt!
mdmackillop. Sorry I was typing my response to xld and you slipped in two new ones. Yes I have downloaded it and did see Ausiebears thread.
However I have been busy here. I will have a look at it now. If I have any questions I will add them to Aussiebears thread.
Thank you for your help.
=Today() is a function which requires the empty brackets as shown. The whole can be enclosed in outer brackets and used in calculations
eg =INT(TODAY()/7)*7
Thanks md I knew that. I guess it was the function that xld had looked a bit confussing and I lost it. I guess I need to just break it down into little bits.
Thank you
I also went back to the other calendar. A bit beyond basic I might try and work through the one xld sent me and try and understand what it's doing first.
Every opening bracket must have a corresponding closing bracket, the trick is getting it in the correct place.Quote:
Originally Posted by peterwmartin
TODAY() is a bult-in function, and as all functions have the ability to define arguments, the () is used to enclose the arguments. Even functions that have no arguments, such as TODAY() still have to follow the syntax rules, so you see an emptys et of parentheses.
If you add a mis-matched bracket, it will certainly error. In your example it will error because the function is not followed by an opening bracket.
BTW, there is an opening bracket before TODAY() and a closing one after it because I am using another function, YEAR(), upon it to get this year number to dynamically build up the dates. YEAR(), unlike TODAY(), does have an argument, a date, so TODAY() returns a date, and YEAR(TODAY()) gets the year of that date.
ANother point, I am using conditional formatting to pick out today's date, and to clear the Sat and Sun cells not in this month.
Xld cell H3 has this formula
I am totally lost with the N2 is it a cell reference.Code:=IF(COLUMN()=2,N2+1,G3+1)
Xld Sorry I found the answer to that. Trying to copy an paste all the cell formulae to notepad to print and look at all together.
Peter,
You can use the CTRL button with the tilde key (just left of the 1 key along the top row of numbers) to display formulas in Excel. Of course, you will need to adjust column width to see them. If you select the range and format with word wrap you will be able to adjust the row height and then print out all the formulas to review.
Regards,