Posts by mrfitness

    I am currently using Excel 2003 to develop a workbook that will be used by many users having different versions of excel.


    I used the Excel calendar in my workbook for one of the sheets (adding the Microsoft Calendar Control 11.0). (Same idea as http://www.ozgrid.com/VBA/excel-calendar-dates.htm)


    I believe when someone installs the calendar, Microsoft puts the file "MSCAL.OCX" onto your c drive, in path "C:\Program Files\Microsoft Office\OFFICE11". (at least it did for me)


    It is used to control the date format that users enter in the cell. When the user clicks the cell, the calendar appears, and once they select the date, the date gets entered in the cell (shown as 17-Nov-2007)


    Everything was fine until I recently tried opening it with Excel 2000 on another workstation and got an error. (I am currently trying to find someone with Office 2000 that can open my workbook so I can enter the complete error message on here) OR it opens without error, but when you click on the cell to activate the calendar, it shows a blank calendar without dates.


    It appears that if a user without Office 2003 tries to open up my excel file that does not have the calendar installed (or the OCX file), they will get an error. Plus different excel versions have different calendar versions (ie 9, 10, 11).


    These users are not very tech savvy so it probably won't be possible to email them the OCX file and teach them each how to copy it onto the proper folder, especially since they are located all over the country and I noticed some users don't have the "Office11" subfolder, only "Office"


    I wanted to know how I could work around keeping my calendar installed in the workbook and avoiding this problem when users with different versions open it. Is there any other calendar function I am not aware of?

    Re: Showing A Sheet While Updating A Different Sheet


    That part worked, but there are other parts of the coding that select cells. I have to try and fix them.

    I have a macro that will be updating many sheets. I have created a plain sheet named "Discount Update" that I want to show while all this other updating is going on. It simply says in cell E8 "Updating Information".


    After I make the "Discount Update" sheet visible, I have used Application.ScreenUpdating = False code thinking it would keep showing the "DU" sheet while I am selecting other sheets and updating cells in the background.


    However, when I run the code, the "DU" sheet does come up initially, but as soon as my code selects another sheet to update cells, it shows that sheet to the user. (it actually shows the 'Surcharges' sheet)


    Anyone have any ideas on how to fix this?


    Re: Stop Cut/Copy Ability In Workbook


    Umm ya I think it was in my coding that I originally pasted.
    What I did was copy that sheet that was giving me problems into a different workbook. But I still was unable to paste. So I inserted a new worksheet in the new workbook I just created, and I was able to paste in that new sheet. The only difference in this new workbook was the first sheet had the coding and the second sheet did not. As soon as I removed all the coding, I was able to paste.

    Quote from Dave Hawley

    No, that is NOT correct at all! On the very page you linked to there is code to re-enable all commands disabled when the Workbook is disabled.

    I wanted to stop users from using cut and paste as well as drag and drop so I found something in Ozgrid to do that. (http://www.ozgrid.com/VBA/disable-cut-copy.htm)


    Problem now is I am trying to run a macro that will copy and paste certain values, but because of the code I have added in the above link, whenever I click on a cell and copy it, when I click any other cell, the copy area gets cleared which of course disallows me from pasting it.


    I tried reversing what I did by enabling what was disabled before, and still no luck.
    Below is what I tried to add to enable the paste function:


    I JUST REALIZED ALL I HAD TO DO WAS ERASE ALL THE CODING I HAD PUT IN, I DIDN'T HAVE TO ENABLE ANYTHING!

    Re: Removing String Quotations From A Variable


    I did read it..and I appreciate the help...but please understand some people just word things better, maybe from natural ability or more experience in these forums. I thought the title "removing quotes from a variable" was a good description of the problem...because that is what i needed to do....I didn't originally want to write down all the code and everything else. I will try my best to be as clear and accurate for future threads.

    Quote from Dave Hawley

    Mrfitness, [fa]*[/fa]

    Re: Removing String Quotations From A Variable


    Hmm good try..I get the error 1004. Method 'Range' of object '_Global' failed


    Quote from chrislayfield
    Code
    aa = ab & y & ":" & z & ab 
             
             'Selection.AutoFill Destination:=Range(activecell:activecell(16,0)), Type:=xlFillDefault
            Selection.AutoFill Destination:=range(aa), Type:=xlFillDefault


    try that and see if it works.


    It would be better if you did all of this using With...End With statements instead of selecting the cells each time

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]Maybe you didn't see when I wrote:


    This gives me a problem when I try to replace

    Code
    Selection.AutoFill Destination:=Range("Z245:Z259"), Type:=xlFillDefault


    with

    Code
    Selection.AutoFill Destination:=rangevariable, Type:=xlFillDefault


    How can I make Range("Z245:Z259") a variable? (I used rangevariable as a name for example) I know how to get the address of the first and last cell in the range already


    Quote from norie

    I don't need to try the code I can immediately see that it's not going to work.


    As to what you want to do, that's not clear.

    Re: Removing String Quotations From A Variable


    Have you tried the code first? It would have helped if you gave an example of what you meant by I have too many quotes :)
    How can I create a concatinating variable without using quotes? To add the word "Range" to variable $Z260 don't you have to use quotes? If I remove the first quote and last one I get an error because Excel is trying to look for a range.
    I cannot even do this without an error:

    Code
    aa = Range(y:z)


    Quote from norie

    Of course you get the extra quotations because you are adding them yourself.


    In fact you seem to be adding far to many.:)

    I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)


    Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.


    I then put this into a String so it would return something like this

    Code
    Range("Z245:Z259")


    Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")"
    This gives me a problem when I try to replace

    Code
    Selection.AutoFill Destination:=Range("Z245:Z246"), Type:=xlFillDefault


    with

    Code
    Selection.AutoFill Destination:=rangevariable, Type:=xlFillDefault


    Below is my total code, but I get stuck on the selection line. I have to do this 300 times so I would prefer not to do it manually!


    I am trying to attach the one file as small as possible for an example but it only goes down to 53K. So I can give you an idea of what the sheet looks like if you Type this below in a blank sheet:


    i need to do this up to MyBox - 150, and in another spot from 1 to 150. i would love to automate it to save me TONS of time!


    hope that helps! thanks in advance

    Re: Disabling Add Or Remove Buttons From Toolbar


    I guess since I changed the coding of the toolbar name so they are ALL PATool you wouldnt have that problem anymore???
    What code were you referring to and where when you said I removed disabling of the Toolbar List?
    Also, the user can still right click and customize :(

    Quote from Dave Hawley

    Because you only remove 1 of your 2 Custom Toolbars.

    Re: Disabling Add Or Remove Buttons From Toolbar


    I understood your first point - I replaced "MyCustom" with "PATool"
    What code were you referring to and where when you said I removed disabling of the Toolbar List?


    Also, if I added RemoveBar to my workbook beforeclose event, how did my custom toolbar get to your application?


    Thanks Dave!


    Quote from Dave Hawley

    You are still using "MyCustom" as the Toolbar name for PART of the code. Also, you have removed disabling of the Toolbar List.


    Also, your Custom Toolbar is now part of MY application. Luckily I know how to get rid of it.

    Re: Disable Custom Toolbar On Any PC


    The reason they got the error is because the custom toolbar was not on their desktop.
    I guess is was looking for the Excel11.xlb file that was located on my computer since I created it. I read that the only way to build a custom one when distributing to users is to build it in VBA. Here is the link that may help anyone else doing the same thing:
    http://www.jkp-ads.com/Articles/DistributeMacro03.htm

    Quote from Dave Hawley

    You stated above that they were NOT able to Disable it as it threw an error.

    Re: Disabling Add Or Remove Buttons From Toolbar


    I put that code into the workbook open event as well as the worksheet activate event and it still doesnt work
    I have attached the spreadsheet. The name of my custom toolbar is "PATool"
    The code works to disable editing the PATool option, but the user can still add or remove buttons, as well as cutomizing the toolbar to remove the PATool and add new ones.
    Please let me know what I am doing wrong.
    Thank you

    Quote from Dave Hawley

    Re: Disabling Add Or Remove Buttons From Toolbar


    Yes I did check the possible answers first...as well as the links within that possible answer.
    That code disables the button inside the command bar I created...I want to disable the right click event as well as the down arrow in the toolbar that allows you to select more toolbars.


    Quote from Sicarii

    http://www.ozgrid.com/forum/showthread.php?t=55033


    This was in your 'possible answers'; does it help? (I don't really have time to go test it)

    I want to restrict users from adding or removing toolbars from the workbook.
    I have created a custom toolbar named xlUtilDemo1 which consists of 4 buttons that appear at the top of the screen. I was able to restrict the movement of the toolbar, yet the user still can click the down arrow of the toolbar or right click the toolbar to customize the toolbar (select other ones).
    below is the code that creates the toolbar and buttons. can anyone tell me how to make the add or remove buttons and the customize toolbar option disabled? thanks

    Re: Disable Custom Toolbar On Any PC


    It seems I was not clear - I noticed you changed the heading to "Disable Custom Toolbar On Any PC" - but that is not what I want to do. I want the toolbar to show up for my users in the workbook I have created, but not to show up on an outside spreadsheet.


    I did do your suggestion, but nothing changes. The user sees a little box of the PAT toolbar located on the spreadsheet area (not even at the top of the sheet), but there are no buttons associated with it so I get a "invalid procedure call or argument"


    Would I make any changes in the Workbook OPEN event? I thought since it was not coming up when the workbook opens up there was a problem there.


    Would I have to recreate these buttons on the Workbook Open event and delete them on the workbook Close event? I would rather not do that so I wanted to know if there was any other way.


    [QUOTE=Dave Hawley]Try;
    Private Sub Worksheet_Activate()

    I created a custom toolbar called "PAT" for users. It has 5 custom buttons-save,import,submit,close,help.
    I attached it to the toolbars as instructed at the site http://www.ozgrid.com/VBA/toolbar-remove-restore.htm


    I need to keep the worksheet tabs showing so I cannot use the

    Code
    application.DisplayFullScreen = True


    For each worksheet that the user will be viewing, i have the following code:

    Code
    Private Sub Worksheet_Activate()
    Application.CommandBars("PAT").Enabled = True
    Application.CommandBars("PAT").Controls("Import").Enabled = False
    Application.CommandBars("PAT").Controls("Submit").Enabled = False
    Application.CommandBars("PAT").Controls("Save").Enabled = True
    Application.CommandBars("PAT").Controls("Help").Enabled = True
    Application.CommandBars("PAT").Controls("Close").Enabled = True
    
    
    End Sub


    ON my workbook activate code and workbook open code, I remove all native toolbars so only the one i created is showing , using

    Code
    Application.CommandBars("PAT").Enabled = True


    On workbook deactivate I have the following code:

    Code
    Private Sub Workbook_Deactivate()
    Application.DisplayAlerts = True
    Application.CommandBars("Cell").Enabled = True 'rclick cell
    Application.CommandBars("PLY").Enabled = True 'rclick ws tab
    Application.EnableEvents = True 'may be False from a Change macro
    Application.CommandBars("PAT").Enabled = False
    
    
    End Sub


    This all works fine on MY desktop, but as soon as I send it to a user they get error on the

    Code
    Application.CommandBars("PAT").Controls("Import").Enabled = False

    line of the worksheet activate.


    The actual PAT toolbar isn't checked off in the list for the user, but if i click Attach it IS already added. For some reason it doesn't show up when another user opens the file. In addition, when a user opens any other excel document, all toolbars remain hidden!


    please help!

    Re: Skip Enable Macro On Open Workbook


    My apologies Dave

    Quote from Dave Hawley

    Rule 14, which you agreed, states NO SUCH questions should be asked! In addition, it shows links to a few ways you can encourage macros to be enabled.


    Please read the rules.