Does anyone out their know the syntax to return the button name or tag when a custom button on a custom menubar is clicked.
Thanks in advance for the help.
Phil
Does anyone out their know the syntax to return the button name or tag when a custom button on a custom menubar is clicked.
Thanks in advance for the help.
Phil
Not a bad suggestion...That would allow allot of flexibility. However, I’d need that to be up all the time, be positioned on the left side of the screen and all workbooks would need to open positioned to the right so this userform does not encroach the worksheet.
What about...
Creating a worksheet that is sized to fit a small area to the left of the screen (with everything turned of like row & column headers, scrollbars etc.) that contains command bars and such. And all other workbooks open slightly minimized to the right of this other worksheet.
Or…
Create a floating commandbar object that is somehow docked to the left of the screen and the workbooks open and are sized as described above.
Just trying to solicit feedback from this incredible international panel of experts.
The ultimate goal is to create a look that is similar to a web page and provide a cool interface that’s easy to use.
Thanks to all,
Phil
Thanks Ivan, I'll give it a try.
Phil
Okay here's a challenge.......
I'm rebuilding an application that has been up and running for several years now and along with many functional changes I have several cosmetic changes that should happen as well.
One thing that I thought I might try actually is both functional and cosmetic. So here's the challenge....
I'd like to create a framed area on the left of the screen that contains my menu's and tools that control the rest of the application. Such as opening files, running macro’s that will effect the open files or accessing databases. I can figure out the functions but, can someone give me a direction regarding the framed window on the left of the screen. This window will need to be visible no mater what worksheet/workbook the user is looking at.
As always any assistance would be appreciated.
Phil
PS…I haven’t seen much of xlDennis lately, does anyone know what happen to him. He is such a great contributor to this forum.
Ivan, Doug,
Thanks for the suggestions. I incorporated both with mods and the result is great. Turns out to be a good method for adding a verity of menus. I do however have an issue everything disappearing when I click a cell to dismiss the pop-up menu. I have tried to find the solution with no luck.
Anyway, I appreciate the help.
Have a good Easter holiday.
Phil
PS...I've attached a copy for your review.
Thanks Roy for the input. However, by the time you posted I had already begun to work on creating my own on screen (in the worksheet) menus. What thought I'd try was to access Excels ability to create (with VB) shortcut menus (such right click menus). Once I have built the menus then I can access each though text box controls that have been placed on the worksheet.
The result is pretty cool. Check out the attached.
Now, does anyone know a way to create a mouse over event, so, when one mouse’s over these controls they change colors and I could also trigger the menu to show and no click would be necessary.
Any help would be appreciated.
Phil
PS...I must give credit to Charlie Kindschi for his face ID tool (it's great for IDing button numbers) and John Walkenbach for his menu code which I modified to create this utility. Thanks to all!!!!!
:cheers:
If your specifically looking for a VBA solution the following code can be placed in the worksheet module...
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C
If Target.Column = 3 Then
Set C = Columns("A").Find((Target), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not C Is Nothing Then
Target.Offset(0, 1) = C.Offset(0, 1)
End If
End If
End Sub
Additionally, one could employ data validation using the list function in the part number column and be able to select a number from a drop down. Better still employ a user form that loads a list box with an array of part numbers and descriptions that once selected will load into the desired cells.
Best Regards and good luck,
Phil
Hi Dave,
First off thank you for replying personally for I've wanted to thank you especially for this site and the people who work it. It is such a tremendous resource for everyone who uses it no matter what level they are at. Dennis, Jack, Roy, Ivan, Richie and yourself (just to name a few) are all incredible.
Anyway, enough kissing up. (hehe, no I really meant the above)
I have been using custom created menus in my applications for some time. What I’d like to do is embed the menus into my sheet that I have design to be the main navigation page for my app. Much like the home page of a website. Now, I could programmatically dismiss the Standard and Format Toolbars along with most of the stock menus which would then cause the custom menus to appear more integrated into the page. But, that can get flakey and create angst among the users.
Ideally, the embedded menu with sub-menus I think is the look and feel that I’m looking for to replace this growing cluster of buttons. I thought someone may know of a control out their that one could apply.
Dave, thanks again for the input and site.
Phil
I'm rewriting an application that has been running for several years now. I'd like to freshen up the look by using some embedded menus with drop-downs instead of the buttons that are currently on the screen.
Does anyone know of some slick (free) downloadable menu active X controls? If so can you point me in a direct to look?
Any direction would be appreciated.
Thanks,
Phil
Regarding working on this together sounds great and I would appreciate the help. So long as you can be patient working with a novice.
I'm pretty much at my wits end on this one. I've tried just about everything I know to try. What makes it even trickier is that the method I have employed actually works every time on some machines, some times on others and never on some.
Now, how do we get started?
Just let me know what I need to do.
Thanks, Dennis
I don’t know how you are compensated for your efforts but I know you put a lot of effort into helping others on this site and I for one don’t take that lightly.
Does anyone know a fool proof way to send mail from excel when the native email service is Novell GroupWise. I have used the sendmail method with sporadic results.
ie…ActiveWorkbook.SendMail Recipients:=OM, Subject:="Requested Job Number"
Additionally, populating the message area does not seem possible.
I have fought this issue for some time. I have tried differant methods, inquired on Novells site and others and cannot seem to find anything that will work every time.
Thanks for anything you can offer.
Phil
Does anyone know a fool proof way to send mail from excel when the native email service is Novell GroupWise. I have used the sendmail method with sporadic results.
ie…ActiveWorkbook.SendMail Recipients:=OM, Subject:="Requested Job Number"
Additionally, populating the message area does not seem possible.
I have fought this issue for some time and cannot seem to find anything that will work every time.
Thanks for anything you can offer.
Phil
I have a question that the experts here can perhaps answer. If I were to acquire an Digital Signature/Certificate and apply it to my Excel applications which are downloaded by various users would this prevent the macro security message from appearing when they open my apps.
If so, how do I apply for one of these things?
Any thoughts?
As usual, thanks in advance for your value feedback.
Phil
Seems that the attachment did not post.
Ian,
Considering we have gone through so many iterations of this file and I have been updating the original based on improvements recommended by Ivan and Dennis I thought it would be a good idea to repost the updated file.
Now, I went ahead and made the modifications that you were interested in (added a button to accept the date, changed the code to pass date to multiple ranges). To see/further modify for your needs please do the following.
In the vba editor…right click on the “CalendarFrm” in the forms part of the tree, then select “View Code”. Navigate down until you reach
Private Sub OKButton_Click()
MyDate = CalendarFrm.Caption
'Change the selection routine below to fit your range needs
Selection = MyDate
Unload Me
End Sub
You can then change the “Selection=MyDate” line to meet your needs. Either add if statements or range references or whatever works for your application.
Hope this helps…Let me know.
Best Regards,
Phil
That's so cool!
Thanks,
Dennis:thumbcoo:
Ivan,
Please use as you fits your needs. It's nice that others see the value and will apply. Especially others like you and Dennis.
Of all the things that I have used of others good examples it feels good to be able to give back. Especially, when quality people appreciate it.
The most humble regards,
Phil
:cheers:
It should have read...
Hello PCFish,
All that you request is very doable. With regard to using the value selected in the calendar…all one needs to do is establish a global variable in the Date variable format and reference that variable in the (command button click routine) But, based on your other criteria I think were getting ahead of each other.
You stated that you did not want to have the dialog dismiss when the user selected the date. Yet you wanted the dialog to display the date and prompt the user for an okay before dismissing. This is done in the following manner…
First you need to
On all the button click routines it states…ActiveCell.Value = D1.ControlTipText you do a find-replace and replace with…CalendarFrm.Caption = D1.ControlTipText (note: replace respective control name with proper name)
This is what was left out… Replace “ActiveCell.Value = D1.ControlTipText” with “Calendarfrm.caption=D1(or respective name).ControlTipText” Then the following will work. Sorry
Now, you need to add a command button (OK) that has the following code attached to it…
Private Sub CommandButton2_Click()
ActiveCell.Value = CalendarFrm.Caption
End Sub
If you want this to effect multiple ranges one simple has to define those ranges in place of the “Activecell.value”.
I hope this helps. If not post another response and I’ll try to help however I can.
Phil
Hello PCFish,
All that you request is very doable. With regard to using the value selected in the calendar…all one needs to do is establish a global variable in the Date variable format and reference that variable in the (command button click routine) But, based on your other criteria I think were getting ahead of each other.
You stated that you did not want to have the dialog dismiss when the user selected the date. Yet you wanted the dialog to display the date and prompt the user for an okay before dismissing. This is done in the following manner…
On all the button click routines it states…ActiveCell.Value = D1.ControlTipText you do a find-replace and replace with…CalendarFrm.Caption = D1.ControlTipText (note: replace respective control name with proper name)
Now, you need to add a command button (OK) that has the following code attached to it…
Private Sub CommandButton2_Click()
ActiveCell.Value = CalendarFrm.Caption
End Sub
If you want this to effect multiple ranges one simple has to define those ranges in place of the “Activecell.value”.
I hope this helps. If not post another response and I’ll try to help however I can.
Fare thee well...
Phil
Thanks for trying...I like someone who is willing to try. PLJ
AJW,
To save you the trouble attached is the revised (corrected) vba date picker file.
Phil