Posts by Jon Field

    Re: Delete Sheet Whose Name Is Defined By A Cell Value


    Hi Dave,


    Thanks for the quick response.


    I have adapted the code to become:


    Code
    Sheets(Sheets("StudentIndex").Range("N3")).Delete


    But it gives me a Run-Time 13 mismatch error.


    Other combinations where I tried referring to the 'StudentIndex' sheet by its number, 2, gave me a Run-Time 9 error.


    The sheet I am referring to has a comma in its name since it the name of student written in the form 'LAST NAME, First Name) taken from my school's central system. Could this be upsetting it?


    Incidentally when I run the error catching code, the name of the sheet I want to delete does appear in the error box.


    Any further help you could give would be very welcome.


    Thanks,


    Jon

    Hi,


    I was wondering if anyone could help with the following problem.


    I have a workbook containing a sheet named 'StudentIndex' in which is a table containing the headings 'Student Name' and 'Link'. Clicking on the the link next to a student's name directs me to a seperate sheet within the same workbook, named with that student's name.


    I am writing a macro to delete students from the workbook. I can delete their details from the 'StudentIndex' sheet but what I can't do is then delete the worksheet that has their name.


    I guess what I need to do is either define a worksheet, say'wsX', with a dynamic name determined by a cell somewhere in the 'StudentIndex' sheet and then simply delete the sheet OR follow the hyperlink to the student's worksheet and then just delete the active sheet. However being a bit of an eegit I don't know how to either of those things.


    Any help you could give would be greatly appreciated.


    Thanks,


    Jon

    Re: Prevent Editing And Selecting Of Range In Newly Created Workbook


    That's exactly what I was after. Thanks, your help is greatly appreciated.


    FYI, the final version of the code was:


    Hi All,


    I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'.


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



    Could anyone please tell me how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.


    Thanks,


    Jon

    Re: Loop Through Rows and Copy Data


    That is awesome thanks Ger...it works perfectly. My apologies, I should have made it clearer, I know next to nothing about vba I am just good at adapting what I find on Ozgrid but this time there was nothing quite close enough and internet tutorials were not clear enough. Anyway, thanks again, and have a good evening,


    Jon


    PS. I will reference you in the code so that anyone checking it knows where it came from

    Re: Loop Through Rows and Copy Data


    What I need is, depending on whether a student has an "x" by his/her name to copy some data about them into a range containing a load of assessment statements and then copy this range into new workbook with an appropriate name.


    What I have written will do this for the first student but not the other 249.


    What Ger has written checks whether the x is there but then only copies over the data from the first row.


    So in short Yes...ish.

    Hi,


    I have some data for my students in the range D2:G251, where each row is a different student.


    I need to write a macro that, if there is an 'x' against their name in column D, will copy the data in columns E-G into range A1:B500 and copy that range into a new workbook and save the workbook with the students name.



    The following code does this for the first row, but I don't know how to write a loop (or whatever) to make it do it for the other 249 rows.



    Any help you could give would be greatly appreciated.


    Thanks,


    Jon

    Re: Automatically Fit Row Height


    Hi Ger,


    Thanks for all your help. I had another workbook open which seems to have been interfering with it. I closed that workbook and now it's fine. Not really sure as there was no code in the other workbook but so long as it works I don't care.


    Thanks again for your time and have a good day,


    Jon

    Re: Automatically Fit Row Height


    Hi,


    Yes it's the line with the AutoFit that brings up the error. Frustrating as I've used it before and it was fine.


    I have tried all three of your suggestions but to no avail. Would hidden cells affect it?


    Thanks,


    Jon

    Hi,


    I have a spreadsheet that will help my students to assess how good they are at various tasks. They choose an option and it gives them a series of statements to match themselves against. Because these statements are of widely different sizes I want to automatically adjust the rows from 6 to 56 to the right height so there isn't too much wasted space.


    I have been trying to use the code


    Code
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Assessment Tool")
    ws1.Activate
    ws1.Rows("6:55").EntireRow.AutoFit


    However I always get the error: "AutoFit method of range class failed". I have tried as many ways as I can think of to define the rows that need AutoFit-ing and nothing seems to work. I have searched both Ozgrid and more widely and couldn't find a specific enough case to learn from.


    The code is part of a larger macro (see below) run from a button and everything else works fine (although I'm sure it's probably not very efficient!). Any help you could give me would be great.


    Thanks,


    Jon



    The whole macro:


    Hi,


    For various reasons I need to copy a the range "a2:s251" in the sheet "FWD Input" to another sheet but I need to remove any formulas.


    To do this I have used the code:


    Code
    Sheets("FWD Input").Range("A2:S251").SpecialCells(xlCellTypeFormulas).ClearContents


    This works fine if there are formulas in that range, however if there aren't it gives me: " Run time error: 1004 No cells were found"


    So I think I need some code that counts the number of cells in the range with formulae in them and either goes ahead if there are some or quits if there aren't.


    Any help you can give would be greatly appreciated


    Thanks,


    Jon

    Re: Autosize Row Height


    Thanks darkyam.


    For some reason it doesn't work yet when I manually autosize them its fine.


    Is this because it Excel won't yet have calculated the contents of the cells when the macro tries to resize them?


    If so, is it possible to build in a delay before doing the resize?

    Hi,


    I have a spreadsheet in which double clicking on any of the cells in the range A4:C17 changes the contents of cells F4:I33. The following code tells me which is the active cell and then pulls the values for F4:I33 from a lookup table based in this.


    Code
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
         
         Range("B100") = ActiveCell.Address()
         Cancel = True
         
    End Sub



    Could anyone please help me out with a snippet of code to stick in this (probably after the third line) that autosizes rows 4 to 33. There are no merged cells involved.


    Thanks,


    Jon

    Hi,


    Sorry I tried searching but to no avail.


    In my spreadsheet cell g1 is a name and cells g3:g6 contain data for that name.


    I have a macro that exports the data in g3:g6 to another workbook and then deletes it.


    What I need is a macro that when I try to change g1, gives me a warning if there is unexported data in cells g3:g6 (ie the sum of them is greater than zero) and gives the option of either continuing or stopping (presumably using a YesNo box).


    Any help you could give me would be very welcome


    Thanks,


    Jon


    PS. I am a VBA newbie

    Re: Print Range Specified By A Cell


    Hi,


    I am somewhat more alert in the morning and have found this link (despite ages searching yesterday):


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


    Following the advice in it I have set the Print_Area (Insert > Name > Define Name > Print_Area) to the following:


    =INDIRECT(CONCATENATE("Browser!$E$2:I",MAX(Browser!$E$4:$E$33)+3))


    Where Browser! is my worksheet and the concatenate function is what I was using to define my range.


    Its ugly but it works.


    Thanks for your help,


    Jon