Posts by mrfitness

    Re: Disabling Certain Properties Of Worksheet Tabs


    how do you mean?


    would that not interefere with macros I created in the book that manipulates data, copies sheets, renames sheets, deletes sheets, etc?

    Quote from shg

    Why not just protect the workbook?

    I want my users to be able to view and select the worksheet tabs in the program I have created, but I want to disable the right click event as well as using the left click to drag the worksheet tabs in a different order. Is this possible?

    Re: Reactiving Right Click Event


    workbook open event?


    Quote from shg

    Code below?


    Maybe

    Code
    application.CommandBars("Cell").reset

    [hr]*[/hr] Auto Merged Post;[dl]*[/dl]

    Quote from mrfitness

    workbook open event?


    i tried it there and didnt work...what was the code that deactivates right clicking on cells?

    I was disabling certain functions on workbook activate, and I noticed i was unable to right click any cell. I used the code below, but that only let me right click on a cell to get options. I still cannot right click on a column or row heading with options coming up
    please help!

    I have a very detailed spreadsheet that users must fill out. I want to create a help button so when the user clicks it, it gives a detailed explanation on what the purpose of the current worksheet is.


    I would prefer to avoid having a message box appear...I would rather have a form pop up with a text box area with a scroll bar for users to read.


    I tried adding a form and putting a text box on top of it. I am not sure if I just type the instructions under the VALUE property of the text box?


    I then added a scroll bar to it, but am not sure how to add the properties of the scroll bar to the text box.


    Help anyone? Thanks in advance

    Re: Hiding Certain Toolbars On A Worksheet


    Thanks for the column and row heading code.
    I did actually look at that link and when I use DisplayFullScreen = True it will not allow me to show a status bar, worksheet tabs, or scroll bars :(

    Quote from Dave Hawley

    No need for all that code just to hide standard CommandBars etc. See Hide Toolbars In Excel and let FullScreen do the work.


    Column and Row headings are at a Sheet Level so you need to add this to ThisWorkbook;

    I am creating a program and want to limit what users can do.
    I ONLY want to show the status bar, worksheet tabs, horizontal and vertical scroll bars.
    I've tried using the code below but the column and row headings will not show on the first sheet i bring up, but it reappears on other sheets. everything else works fine

    Re: Calling A Sub With Variables


    I change the 34 values to match the drop down cell link on the first sheet. I use each cell link in a vlookup to return a value I need to work with in VBA.


    Quote from mikerickson

    Are you changing those 34 cells for the custom case and then calling the Change routines to restore the 34 values from the dropdowns?

    Re: Calling A Sub With Variables


    It is really hard to explain. There are a few worksheets involved.


    There is a drop down on a sheet called custom options,with a cell link on sheet Lookup!$K$9.
    I have 34 identical drop down lists on another worksheet surcharges, with cell links also on worksheet lookup, cells E3 to E3 to E34.


    Basically if the user selects a drop down on the custom options sheet (other than the first one) i copy the cell link of it (Lookup!$K$9), and I paste it over the cell links of the 34 idenitical drop downs. (E3 to E34)


    Each of these dropdown boxes on sheet surcharges has a change event...but since i am changing the cell link values first, i have to call each dropdown change event in order for it to do its thing.
    Confused?


    Quote from shg

    It might help to back up and explain what you're trying to do, rather than how you're trying to do it.

    Re: Calling A Sub With Variables


    Ya don't worry about that...it was only a test so I omitted it. (You are right with that it did)
    I originally stated I am having a problem with the call y function. I want to avoid the code below:


    Quote from Sicarii

    Aren't you adding 4 to X? so 4+1=5 or 4+2=6...

    Re: Calling A Sub With Variables


    I get an error saying "the macro dropdown1_change() could not be found'


    I want it to run a sub in the same module. What am I doing wrong?


    Quote from shg

    I think the syntax you want is

    Code
    Run "dropdown" & X & "_change()"


    ... but I think you could do better than writing 46 near-identical subs ...

    I have 46 drop downs that I need to call from a main sub. I want to create one loop that will call each one to perform its function. Below is a simple example of what I am trying to do, but where I need help is with the 'Call Y' line. I am not sure what character to use to tell excel to look at what is in the value of Y, not 'Y' itself

    Re: Drop Down Click To Unprotect Worksheet


    Thanks Ger! You are right it was that the column M was locked...I knew it had to be something simple.

    Quote from Ger Plante

    I concur with Bill - took me about 30 minutes to figure it out though... The cell link field is linked to a cell that is locked.


    Ger

    Re: Drop Down Click To Unprotect Worksheet


    I did have it in the change event of the drop down before I posted my problem here...and I did add it to the change event of the worksheet but still get the Microsoft error. I did not do the workbook open and close as suggested below as I have MANY worksheets involved, but as a test I DID try it and I once again get the MS error. :(

    Quote from Ger Plante

    Hmm. thats odd.. You could try it on the change event of the drop down, or the change event of the worksheet.... at worst case you could put it in the workbook open event, but I wouldnt recommend this approach until all else fails -


    Ger

    Re: Drop Down Click To Unprotect Worksheet


    That makes sense but I still get the microsoft error.
    I think it should be in a worksheet event instead of a dropdown event? Or maybe instead of dropdown CHANGE event it should be a select event or click event?? any suggestions?

    I have a worksheet that is initially protected when a user views it. Certain cells are unlocked.
    I have 46 drop down lists on the worksheet, of which none are locked so the users can access them for selections.
    I want to unprotect the sheet when ANY of the dropdowns are clicked on.
    For each change event I have the following code:

    Code
    Sub DropDown34_Change()
    
    
    Application.EnableEvents = False
    ActiveSheet.Unprotect


    However I still get the microsoft error screen showing up telling me that the cell is protected BEFORE it unprotects it and allows me to make changes.
    Any idea as to what code and where I should put it to avoid getting that microsoft message first? Worksheet_Activate?

    Re: Today() In Vba Code


    Thanks!
    Below is the code that REALLY works! I even have the calendar coming up if the date is out of range to re-select


    Quote from turtle44

    In VBA use "Date" to get current date, not "Today".


    Code
    'x = date(today())
    'replace the code above with the following:
    x=Date