Posts by IanDunnett

    Re: Automate Input Login & Password


    I've got a workbook that does exactly this.


    You need to do a scan on the login page of the website to find out what the boxes are named as in the HTML coding.


    You can use this to get the names of all the things on the site...



    Then when you have the names of the items for the username, password and log in buttons you can use something like;
    Say your log in name in written in cell A1 of sheet1 and the password is in cell A2 of sheet1.




    hope that helps,
    Ian

    Re: Delete Entire Row For Data Not Meeting Criteria


    Hi,


    This is a basic answer but should do what you need;




    Regards,
    Ian

    Re: Set Different Page Title For Each Printable Page Of A Worksheet


    If you go to the file menu in Excel then go to page setup.
    On the 'sheet' tab you can set an amount of rows to be printed at the top of each page.


    This is used when tables wrap over pages but you want to see the headers on each printed page. (This can alos be done for columns on very wide tables)


    Hope that helps,
    Ian

    Re: Prevent User From Accessing Shortcut Keys


    Hi There,


    The only way I know of turning off shortcut keys is using the Application.Onkey command, although to do this for all shortcut keys you would have to write a line that disabled them each in turn, and had a similar macro on the close event to turn them all back again.


    This can only be used to turn off shortcut keys specific to Excel. System shortcut keys like Ctrl + Alt + Del or Alt + Esc can't be altered this way (that I know of).


    Cheers,
    Ian

    Re: Reference Cell By Content


    Hi Jonwoods, welcome to Ozgrisd,


    Please remember to use code markers otherwise the administrators won't be happy with you.


    I think what you want here is one summary sheet to show all the results from column A:J in your other sheets.


    To do this I would set up a loop for all the sheets that aren't the summary sheet and also set a range to find the next empty cell in column A each time.


    Something like;


    Hope that helps,
    Ian

    Re: Prevent Worksheet Scrolling


    I've got this working now;


    The main problem wasn't the fixing of the scrollarea but the fact I had all the unused rows hidden.


    It turns out if you have rows hidden then you can still scroll around and make most of the screen appear as just the greyness because effectively you're not scrolling to the area that isn't allowed...


    After unhiding the rows and then refixing the scrollarea by using a quick end(xlup) to find the last row (I know the last column) everything is now ok.


    Thanks for chipping in Dave and Pasacal.
    Ian

    Re: Prevent Worksheet Scrolling


    Hi Pascal,


    The use is slightly more complicated than that.
    The spreadsheet needs to be editable and has certain validation acting on it to control the usetrs inputs. The data they enter is then captured and put into another database from a macro which reads the tables.


    The issue I have on scrolling is purely an aesthetic one, but if we can fix it, it will save my team from having to check that the tables are right before they are viewed in meetings.


    Dave - Your code is an ok idea but it needs to execute when the workbook is deactivated or to fix the scroll area when the workbook is activated. Any ideas? I have tried fixing the scroll area just to what I want to remain visible however this seems fine in a standard Excel workbook, I can still scroll past the range when I am in the embedded sheet in Powerpoint...
    very confusing...


    Ian

    Hi Everyone,


    I have an excel sheet embedded in a Powerpoint presentation, but I have the problem that users are editing the sheet and accidently or unknowingly scrolling around. This results in the window resizing itself when the exit the object and messing up the appearance of the presentation. Is there a way of easily preventing scrolling (either via VBA or other)?


    I can obviously hide the scroll bars but that does not prevent the use of the mouse's scroll-wheel.


    I have also already hidden all unused columns and rows so that they cannot oversize the window.


    All thoughts appreciated.
    Thanks,
    Ian

    Re: Macro Runs Fine But Not On Open Workbook Event


    Yup still errors without the do events. I actually put that in there as a fix attempt.


    I thought maybe it was something to do with the named range and that in the opening of the workbook maybe the named ranged hadn't been assigned by the time the macro started to run, but I have since tried looping using cells rathetr than the range or even doing a find for the username in the column where the range is and both of those fall over too, so I am pretty convinced it has something to do with referencing the hidden sheet, or the sheet Map in particular.


    :confused:

    Re: Macro Runs Fine But Not On Open Workbook Event


    Hi Parsnip,


    The top one;

    Code
    For Each GovRng In Sheets("Map").Range("GovernanceMembers")


    I've tried putting other things in there and it seems to have something to do with referencing the 'Map' sheet...not sure why though. The Map sheet is hidden when the workbook is opened.


    Any thoughts? Thanks,
    Ian

    Hi Guys,


    Got a strange one here...
    I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.


    The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.


    The code is below;



    Any ideas much appreciated. Cheers,
    Ian

    Re: Import Bottom x Rows Of File


    I think the easiest way to do this is import all of the data however you are doing that at the moment (I'm sorry I don't really have a lot of familiarity with that bit) and then have a marco that runs to delete all but 'x' rows at the bottom.


    For example...


    Any help?
    Ian

    Re: Embed Worksheet In Powerpoint And Control


    Andy,


    If you weren't a bloke, and I didn't have a girlfriend and you were here right now I could kiss you.


    That really helps...again!


    I was playing around with properties of the OLEObject rather than going for OLEFormat. Can you tell me what the difference is and when each applies? It would really help me out and probably stop me from from bothering you in the future.


    Also; Are all the usual excel worksheet and associated properties avaiable through the;

    Code
    ExTbl.OLEFormat.Object.Worksheets(1).Property.SubProperty.etc.etc


    method or are there restrictions in using the embedded sheet?


    Cheers,
    Ian

    Hi Everyone,


    There's a couple of threads on this type of thing but I can't seem to get this working...


    I want to initiate Powerpoint from Excel using VBA and then on a certain slide in a powerpoint template embed an excel worksheet and copy some content (but not Link it) from excel to the embedded sheet.


    So far I have got to;


    but I can't get the syntax right to copy data into the embedded sheet.


    Any help always appreciated.


    Many thanks,
    Ian

    Re: Macro That Opens Another File And Run The Macro In That File


    Hi mishima_uehara,


    I'm confused by your code...


    You use lines like;


    Code
    place = Workbooks(main & ".xls").Worksheets(1).Cells(lin_conv, COL_CAMINHO)


    But the variable main is assigned as the name of a worksheet, not a workbook!?


    Richie has pointed you in the right direction. Let us know how you get on.
    Ian

    Re: To Use Worksheet Name Dynamically In A Formula


    If you want to avoid the VBA route you could use something like the below;


    =IF(ISERROR(INDIRECT(A9&"!C14")),0,INDIRECT(A9&"!C14"))


    That way it will just give a 0 for any sheet that doesn't exist yet, which is useful as then you can sum across the whole year-to-date at any stage.


    Cheers,
    Ian

    Re: If Condition Formula


    Hi Tiffany,


    You seem to have the speechmarks slightly out of place and it might be advisable to stick to decimals rather than %'s.


    Also with the if function you only need to state one logical test per if function. If you want two you need a nested if function.


    One If Function:
    =IF(A2<0.2,B2/0.8,C2*0.02)


    Two If Functions
    =IF(A2<0.2,B2/0.8,IF(A2>0.2,C2*0.02,"Something Else"))


    The 'something else' statement would only come into use if neither of the conditions were met i.e. A2 was exaclty equal to 20% rather than greater or less than it.


    Hope that helps,
    Ian

    Re: Decrease Cell Value


    If your userform is called userform1 and your textbox is called textbox1 you could use something like the below placed in the code page of the userform...


    assume your sheet with the two cells you want to change is called Sheet1


    Code
    Private Sub TextBox1_AfterUpdate()
    
    
    Sheets("Sheet1").Range("E4").Value = Sheets("Sheet1").Range("E4").Value - 1
    
    
    End Sub


    Cheers,
    Ian

    Re: Protect A Shared Workbook


    Hi Chris,


    The only thing I can think of is to use the buttons to toggle a variable which then reads into a macro running on the workbook which has defined ranges which need to be protected.


    Something Like (for the command button, with 'Editable' as a boolean varible declared as public)


    Code
    If Editable = True Then
    Editable = False
    Else:
    Editable = True
    End If


    then in the code section for each sheet, something like;



    Try typing 'Features that are unavailable in shared workbooks' into Excel's help to find out more on the disbaled functionality (and some alternatives) in shared workbooks.


    Hope that helps,
    Ian

    Re: Counting Weeks And Days


    Hi Gary,


    I would be careful with using your integer formula as that will round-up/down rather than giving you the whole number of weeks, I would opt for the floor function instead.


    =FLOOR((B4-A4)/7,7)


    Should give you the number of whole weeks. Then to calculate the number of days on the end you could either use the number you've just calculated and work out the remainder;


    =(B4-A4)-(7*(FLOOR((B4-A4)/7,7))


    or use the mod function wqith a divisor of 7;


    =MOD(B4-A4,7)


    The only comment I would make on these methods is that if you go between Wednesday and Wednesday i.e. July 24th to July 31st, you will get the result 1 week and 0 days whereas you may actually want this to read 1 week and one day, so dependent on how you intend to use the result you may have to add 1 day to get your real answer.


    Hope that helps,


    Cheers,
    Ian