Posts by dodger7

  • Show List Depending On Drop Down Selection

    Re: Show List Depending On Selection From Drop Down Menu


    Hi Bill


    many thanks for your reply. Im new to VBA, had a few lessons but trying to get time is a nightmare the now. Your solution worked as needed many thanks. Hopefully ill get time to learn about userforms etc after the new year, as im not fully understanding your reply.


    It works though, Thanks again!

  • Show List Depending On Drop Down Selection

    Hi folks how do?


    Im currently creating a spreadsheet for my work, with common problems and solutions to one of our websites.


    When the spreadsheet is opened, the user will click a button depending on what sort of problem he/she is having. In the example attached, if the user clicks on "make payments" a form will appear. This will have a list of problems.


    Where I need your help is, if the user choses problem 1 from the drop down list, then I would like the list of solutions to problem 1(from the MPData sheet) to show in the box below. The same goes for problem 2, problem 3 etc.


    Is this possible?


    Many thanks


    Jamie

  • Password Protect Sheet From Being Viewable

    Re: Password Protect A Sheet From Being Viewable


    ps. what line of code can I add so that if the user clicks "cancel" when the password prompt box appears, they will be returned to the front page (which is the page they wil be on before they click the button prompting for a password?)

  • Password Protect Sheet From Being Viewable

    Re: Password Protect A Sheet From Being Viewable


    many thanks for both replies. The code works as needed.


    Do you have any suggestions of a more safer way to protect data? I think the current users of the spreadsheet will be fine, but future work may be done for more experienced excel users.


    Many thanks for any sugegstions
    Jamie

  • Password Protect Sheet From Being Viewable

    Hi all


    I am currently cerating a spreadsheet for my department at work. It will be for all to use but one department in particular will have sensitive data on it. I had done a search on this but could only find information about password protecting a sheet by means of selecting a sheet, rather than from a button.


    I have attached my spreadsheet that ive just started (as you will see!)


    When the user clicks on the E-Commerce button I would like a password prompt box to appear. Can someone kindly help me with this?


    I tried to use the code I found on search, and adapt it from selecting a sheet to clicking a button, but failed miserably.


    Many Thanks in advance
    Jamie

  • Report Data Spread Over Many Worksheets

    Re: If Formula/vba For Non Blank Cells


    Roy, do you have an example of a similar pivot table you could upload? Im unsure what you mean?


    I apologise for asking what seems a stupid question, but as youll hopefully see from my spreadsheet ive tried my hardest to do it without asking questions! in the future ill try and plan it better fro the start

  • Report Data Spread Over Many Worksheets

    Re: If Formula/vba For Non Blank Cells


    p.s a pivot table would be ideal if each person didnt have different projects, but project 1, project 2 etc will be overtyped but every team member with their own projects, thats why a pivot table is no good. cheers

  • Report Data Spread Over Many Worksheets

    Re: If Formula/vba For Non Blank Cells


    the reason for so much sheets is that theres 40 people in my team, and theres 52 weeks in a year so to try and have all the data on the one sheet would be quite difficult - especially since the project names will change weekly. I fugures if each team member had their own sheet, then it would be much more user friendly as the weeks go on.

  • Report Data Spread Over Many Worksheets

    hi people


    im creating a spreadsheet at work that gathers project and FTE figures.
    I have attached a copy, but ive had to totally simplyfy it to get it to the required uploadable size.


    Basically each team member goes into the sheet tab with their name on it (the full version has about 40 sheets), enters the total hours per week they will be spending on each project.


    Then the manager opens the spreadsheet, clicks on get data then enters the date. Data for the selected date is then shown. However I need excel to go into each persons sheet, take the names of the projects that will be worked on (obviously the ones that are not blank) then report the names onto the front sheet under "name of projects".


    can anyone help me with this? as you will see, im self taught with vba etc so its probably not the best but im trying.


    many thanks for any input
    jamie

  • Formula Added By Macro Displays Formula Not Result

    Hi people

    ive got the following code

    Code
    Range("H13").FormulaR1C1 = "=VLOOKUP(totals!A1,totals!B4:N26,2,False)/35"



    but once the formula has been put into the cell, it copies over as

    =VLOOKUP(totals!'A1',totals!'B4':'N26',2,FALSE)/35

    adding in apostrophes before and after each cll reference. Is there a way I can stop this?

    Many Thanks

  • Stop Scientific Format For Large Numbers

    Re: Code To Automatically Add A Zero To The Start Of A Number And Format The Column


    Dave, thanks for your input. Custom format "0"# works but on this particular spreadsheet I am not able to format cells as its disabled. I was going to use your other suggestion ="0"&A1 which also works, but again on this spreadsheet I am unable to insert a column. Ill maybe try and speak to someone about enabling "format cells", something as simple as this should not require code. If there wasnt 20 thousand rows I wouldnt mind :)

    Thanks again

  • Stop Scientific Format For Large Numbers

    Re: Code To Automatically Add A Zero To The Start Of A Number And Format The Column


    Yeah I can format the cell to get it to show as 970702090341 but I need excel to put a zero at the start of the number aswell. Plus, The "Format Cell" fuction is disabled in this particular workbook, so Ideally I wanted a code to do this for column G. Me being an amateur with VBA I played about with some code but couldnt come up with anything.

    Thanks
    Jamie

  • Stop Scientific Format For Large Numbers

    Re: Code To Automatically Add A Zero To The Start Of A Number And Format The Column


    unfortunately I cant change the import - that comes from a different source. Yeah its really stupid that the "admin" have disabled most functions and yet not disabled VBA! I think its to protect the format of the other worksheets more than anything, and as opposed to just protecting the cells thats supposed to theyve just done the full wokbook. Im currently trying to get a hold of the idiot that did it, but in the meantime will need a solution

  • Stop Scientific Format For Large Numbers

    Re: Code To Automatically Add A Zero To The Start Of A Number And Format The Column


    thanks again for your reply. after its formatted I will be copying the figures into my system at work, but it only recognises the figures as valid if there is a "0" at the start. Is there code I could use to format column G cells to "0"#? The reason I ask is that format cells have been disabled (but VBA isnt!)

  • Stop Scientific Format For Large Numbers

    Re: Code To Automatically Add A Zero To The Start Of A Number And Format The Column


    sorry if its not explained entirely. There will be figures imported into column G. I need a zero at the start of the figures without going down every row and adding a zero to the start of them manually. I also need it formatted by code as most of excels features have been disabled as the rest of the spreadsheet is formatted a certain way and cant be changed.