Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
I am struggling with excel command buttons that move around my
worksheet when rows and columns are hidden. I need for them to move with the cell and resize, as I need them to be hidden at certain times. My program is used to keep scores in the fall season and I am 1/3 through the season. Is there a way that I can write a macro to place (or actually in this case replace) the rogue command buttons back in their intended specific location and reset the size, if necessary to its original size? This would solve my problem quickly, but I do not know if it is possible to write such a macro. I have experimented with the macro writer, but it seems to want to relocate by scaling from where the button is. Since I do not know where the darn button is, that does not work for me. As an example, I would need a macro to do the following... 1) Locate the existing command button (Button 1) in cell E26 so the left edge and top of the button are at the cell left edge and top of the cell (the button has moved to the top edge of the page) 2) Make the command button (Button 1) 1" wide and .25" high (not sure if I need to do this or not, because I am not sure if the command button stayed the same size when it moved - I will have to trial and error to find out when I figure out how to move the button). I would really appreciate some help. I am self-taught and I am partially blind. My users are unskilled moms using the program every week, and on some oftheir computers the buttons move, and on others they do not. (talk about the blind leading the blind :-) - just kidding). I suspect it is an available memory issue, but am unsure, as older computers and netbooks seem to have the problem... I need a quick fix, and could place a similar macro in front of the call for each button (There are close to 100 buttons on the sheet - only a couple are supposed to appear at a time). Is this possible? Thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
Assuming you are using an activex command button:
With Worksheets("Sheet1").OLEObjects("Button1") ''Change sheet/btn names as needed .Top = Worksheets("Sheet1").Range("E26").Top .Left = Worksheets("Sheet1").Range("E26").Left .Width = 72 .Height = 18 End With If it's a actually a Forms toolbar button change the first line to: With Worksheets("Sheet1").Buttons("Button 1") It would be better to assign a range name to E26 and use that in the code so you could e.g. insert a row if needed later and not have to revise your code. "RJQMAN" wrote in message ... I am struggling with excel command buttons that move around my worksheet when rows and columns are hidden. I need for them to move with the cell and resize, as I need them to be hidden at certain times. My program is used to keep scores in the fall season and I am 1/3 through the season. Is there a way that I can write a macro to place (or actually in this case replace) the rogue command buttons back in their intended specific location and reset the size, if necessary to its original size? This would solve my problem quickly, but I do not know if it is possible to write such a macro. I have experimented with the macro writer, but it seems to want to relocate by scaling from where the button is. Since I do not know where the darn button is, that does not work for me. As an example, I would need a macro to do the following... 1) Locate the existing command button (Button 1) in cell E26 so the left edge and top of the button are at the cell left edge and top of the cell (the button has moved to the top edge of the page) 2) Make the command button (Button 1) 1" wide and .25" high (not sure if I need to do this or not, because I am not sure if the command button stayed the same size when it moved - I will have to trial and error to find out when I figure out how to move the button). I would really appreciate some help. I am self-taught and I am partially blind. My users are unskilled moms using the program every week, and on some oftheir computers the buttons move, and on others they do not. (talk about the blind leading the blind :-) - just kidding). I suspect it is an available memory issue, but am unsure, as older computers and netbooks seem to have the problem... I need a quick fix, and could place a similar macro in front of the call for each button (There are close to 100 buttons on the sheet - only a couple are supposed to appear at a time). Is this possible? Thank you. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
Robert, have you considered Don's suggestion to get rid of the buttons
and just have users double-click a cell. Test for this in the BeforeDoubleClick event and run a macro based on cell address using Select Case. FYI, my copy of your workbook doesn't behave the way you describe so is this consistent with all users or just some? It's hard to remedy a situation that I can't duplicate! I tried all kinds of zooms and every button is always exactly where it should be. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
On Oct 6, 4:31*pm, GS wrote:
Robert, have you considered Don's suggestion to get rid of the buttons and just have users double-click a cell. Test for this in the BeforeDoubleClick event and run a macro based on cell address using Select Case. FYI, my copy of your workbook doesn't behave the way you describe so is this consistent with all users or just some? It's hard to remedy a situation that I can't duplicate! I tried all kinds of zooms and every button is always exactly where it should be. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hello Garry... I do not know how to do the 'before double click event' on a cell. As I say I am self taught, which means my teacher did not know any more that I did. I am recovering from eye surgery today, but I am able to see a little better (except for dozens of 'floaters' so perhaps this time I will be able to read your solution without someone to help me. It would be another and probably better solution. I have, today, gone over every one of the hundreds of buttons and locked them in by positioning them every time that portion of the sheet or sheets is viewed. After doing this, the problem appeared at least at first glance pretty well solved. But not 100%. I was only able to duplicate the problem when I was playing with the worksheet, unhiding the calculation columns on the enter scores page. And when I rehid the columns and went back to opening the sections from the buttons, it all worked again. Whew. Could you give me an example as to how to do the before double click event? I just do not have the background that I wish I had - I can figure out 95% of the things by studying the recommendations on this group and reading John Walkenbach's book for dummies, which has been very helpful, but it is mostly trial and error. Garry, I am not secure with the fix I have implemented, and the fix you suggest sounds a great deal more secure. As to the problem, I have several old computers here, and the problem occurs on those with less memory. It seems unrelated to the zoom - I thought perhaps it was the zoom because of the earlier excel problem a few years back. It occurs on my netbook and an old Sony Viao that I inherited. It is not consistent, but I can duplicate it. The problem does not occur on my new computers. It did occur this weekend on one school-owned laptop in Inverness Florida. They had several instances of Excel running as well as some other incidental programs. They rebooted and only ran the scoring program and had no further problem. Feel free to contact me directly - you have my e- mail. I feel a bit guilty tying up the group with my one problem. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
After serious thinking RJQMAN wrote :
Could you give me an example as to how to do the before double click event? Not to discount the value of Don's suggestion (not mine), I advocate that a context-sensitive menu approach would be better whether a custom toolbar, menus, or cell popups. This is definitely more complex than Don's suggestion, but would be a great benefit to the program given its already complex structure. I will, however, look at the ContestData buttons and see how they can be replaced with the SheetBeforeDoubleClick event. I'll email you my results tmo (Sat). I'll look at EnterScores too! As to the problem, I have several old computers here, and the problem occurs on those with less memory. It seems unrelated to the zoom - I thought perhaps it was the zoom because of the earlier excel problem a few years back. It occurs on my netbook and an old Sony Viao that I inherited. It is not consistent, but I can duplicate it. Yeah, I figured that's where this issue manifests. Seems like you've addressed the crashing issue only to reveal there's still more contributing things to clean up yet. The oldest machine I have here (circa 2002) has 1GB memory and so I doubt it will manifest there unless I load resources with other stuff. Problem is your project resource intensive (has tonnes of buttons and tonnes of formatting). I've done dozens of projects with 4 to 5 times as many sheets, all with tonnes of formatting, and your file is more than double the file size. Most of that is due to the controls and numerous images. BTW, where can I get an original of the wizard image? Feel free to contact me directly - you have my e- mail. I feel a bit guilty tying up the group with my one problem. Actually, you're better off to continue here since there's others here (no doubt there) who can shed light on lots more stuff than I'm able to. I do agree that the other things we're doing should happen outside the forums. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
On Oct 8, 1:37*am, GS wrote:
After serious thinking RJQMAN wrote : Could you give me an example as to how to do the before double click event? Not to discount the value of Don's suggestion (not mine), I advocate that a context-sensitive menu approach would be better whether a custom toolbar, menus, or cell popups. This is definitely more complex than Don's suggestion, but would be a great benefit to the program given its already complex structure. I will, however, look at the ContestData buttons and see how they can be replaced with the SheetBeforeDoubleClick event. I'll email you my results tmo (Sat). I'll look at EnterScores too! As to the problem, I have several old computers here, and the problem occurs on those with less memory. *It seems unrelated to the zoom - I thought perhaps it was the zoom because of the earlier excel problem a few years back. *It occurs on my netbook and an old Sony Viao that I inherited. *It is not consistent, but I can duplicate it. Yeah, I figured that's where this issue manifests. Seems like you've addressed the crashing issue only to reveal there's still more contributing things to clean up yet. The oldest machine I have here (circa 2002) has 1GB memory and so I doubt it will manifest there unless I load resources with other stuff. Problem is your project resource intensive (has tonnes of buttons and tonnes of formatting). I've done dozens of projects with 4 to 5 times as many sheets, all with tonnes of formatting, and your file is more than double the file size. Most of that is due to the controls and numerous images. BTW, where can I get an original of the wizard image? Feel free to contact me directly - you have my e- mail. *I feel a bit guilty tying up the group with my one problem. Actually, you're better off to continue here since there's others here (no doubt there) who can shed light on lots more stuff than I'm able to. I do agree that the other things we're doing should happen outside the forums. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Hi Well, I have rewritten the code for the contest data page and replace about 90% of the buttons with color-filled cells. I did not understand the 'before click' thing, but when researching it, I found the SelectionChange(ByFVal Target as Range) command, and it seemed to work just fine, so I used it. I rewrote the contest data page - using the following code for the cells that are masquerading as buttons in the worksheet module and it seems to be working fine. I can hide or display the line without worrying about the buttons going off the page on their own. The following is typical for one button - as you know, there are perhaps 100 buttons on the page - the code is in the worksheet module. Since one sub often calls another, I deposit the cursor in cell D2 - then added the exit sub statement to prevent the code from re- triggering a second series of actions; Private Sub Worksheet_SelectionChange(ByVal Target As Range) ' ' === ESCAPE IF 2ND SUBROUTINE WOULD CANCEL 1ST === ' If Target.Address = "$D$2" Then Exit Sub ' To prevent looping ' I drew buttons using perhaps four adjacent cells with one border around them, and filled them with a color and a descriptive text so they looked like Command Buttons. I found I could not merge them and still have the code work, so I just did the following, which seemed to work fine. If Target.Address = "$BD$75" Or Target.Address = "$BE$75" Or Target.Address = "$BF$75" Or Target.Address = "$BG$75" Then Call FMBC02_OpenWizard_02 The worksheet module contains a similar statement for each group of cells - perhaps 50 to 75 such statements in the same sub. I left some of the buttons as Command Buttons, because I grey-out the text and I did not want to cause some other kind of problem that I did not understand if I tried to grey-out the text in the cells that were triggering the macro(s). So in a regular module, I repaint and relocate each Command Button every time I open the section of the worksheet that contains the button or buttons (an example follows), using the code Jim Rech was kind enough to supply above. This brings the Command Buttons(s) back where they belong if they jumped to the edge of the sheet, as they were prone to do before. The following code is word-for-word from Jim's recommendation, with my button number and subroutine call substituted; With Worksheets("Contest Data").Buttons("Button 71") .Top = Worksheets("Contest Data").Range("BI153").Top .Left = Worksheets("Contest Data").Range("BI153").Left .Width = 35 .Height = 18 End With The code Jim provided works very well and locates the Command Buttons at the left top edge of the cell. I played with the code to see if I could locate the button perhaps indented from the edge, or located off the right or bottom edge, but could not figure out a way to do it. Since it was not critical, and my time was limited, I just went with it as Jim provided. I had to change the Protect command for this sheet by adding a statement allowing the user to access both protected and unprotected cells in order for the code to work - so I did not want to use this system on all the pages, as on at least one of the pages (Enter Scores, specifically), it is not helpful for the user to be able to access protected cells - it is far better for them to just access the cells where they enter the scores. Otherwise they would be constantly getting the 'protected cell' message, so I stayed with the Command Buttons on that page and did not change over to the cell system. It also seems to be working OK so far. In response to your question, about the Wizard - I used a royalty-free clip-art for the Wizard and then using the really great free shareware from PaintNet, pasted in the trumpet from another clip art. This is the only 'imported' image used. I use circles as buttons for the HELP files, and now I am using cells as buttons on many sheets, and command buttons as buttons on others. Just as you indicate, the program does appear to 'eat up' a lot of computing power. Some users have found that they cannot print the results because the print won't format - even manually. I think this is due to lack of resources in their computer, but that is just my guess. Each time they have been able to make it all work, though, by closing the program and re-opening it. I am guessing that is due to the program using all the available resources, but I am not certain at all, and I do not have the slightest idea how to reduce the demand on resources, but I wish I could do that so the program would be more 'bullet-proof.' Today is a Saturday, and the program is being used all around the states of Florida and Louisiana. I only received one call, and that was related to the printing problem mentioned, and the problem resolved when she closed Excel and re-opened it. Garry, I do all of this for no charge for a non-profit organization and for the kids, and I deeply appreciate the help. Thank you a thousand times over. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
Robert,
It's good to know your users haven't had much problem to date. I can appreciate all the extra work you did to eliminate the buttons. Jim's code is very helpful for relocating any that might get moved. Seems, though, that for the remaining buttons it would require a lot of coding. Perhaps a loop would be more efficient and easier to maintain. I'm thinking you could store the button names and their cell addresses in a string constant as delimited value pairs... Sub ResetButtons() Dim n As Long, v As Variant, iPos As Integer Const sButtonData As String = _ "Button 71:BI153,Button 72:BI154,Button 73:BI155" 'edit to suit v = Split(sButtonData, ",") For n = LBound(v) To UBound(v) iPos = InStr(1, v, ":") With Sheets("Contest Data").Buttons(Mid$(v, 1, iPos - 1)) .Top = Sheets("Contest Data").Range(Mid$(v, iPos + 1)).Top .Left = Sheets("Contest Data").Range(Mid$(v, iPos + 1)).Left .Width = 35: .Height = 18 End With Next 'n End Sub I'm making progress on the userform version of the contest setup wizard. Can you email me the image file of the wizard? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
URGENT help needed for a macro to place and size command button
GS expressed precisely :
Oops! I made a mistake. Revise as follows... Sub ResetButtons() Dim n As Long, v As Variant, iPos As Integer Const sButtonData As String = _ "Button 71:BI153,Button 72:BI154,Button 73:BI155" 'edit to suit v = Split(sButtonData, ",") For n = LBound(v) To UBound(v) iPos = InStr(1, v(n), ":") With Sheets("Contest Data").Buttons(Mid$(v(n), 1, iPos - 1)) .Top = Sheets("Contest Data").Range(Mid$(v(n), iPos + 1)).Top .Left = Sheets("Contest Data").Range(Mid$(v(n), iPos + 1)).Left .Width = 35: .Height = 18 End With Next 'n End Sub Sorry about that! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command button font size changes | Excel Discussion (Misc queries) | |||
command button code needed | Excel Programming | |||
Help needed moving a command button | Excel Programming | |||
Bug: Command Button Text Size Changes | Excel Programming | |||
Urgent: VBA Command Button Caption Problem | Excel Programming |