Posts by Simon Lloyd

    Re: Run Macro Within Worksheet_change Event

    Remove all of your sub that you have inside the worksheet_change sub and put it in a standard module then in your worksheet change under

    If Target.Value > 0 Then

    type Call Refresh_PivotTables

    Added by admin

    Sub RefreshAllPivots()
    Dim wSheet As Worksheet
    Dim pt As PivotTable
        For Each wSheet In Worksheets
            For Each pt In wSheet.PivotTables
            Next pt
        Next wSheet
    End Sub

    Re: Combobox Properties to determine next selection after enter

    A combobox is not located in a specific cell regardless of how neatly you may have made it fit, it is located at a reference point on a sheet, the best you could do is SELECT or ACTIVATE the cell next to it, so if the combobox covers cell A1 then using this code in the worksheet module

    Sub combobox1_Change()
    Range("A2").Activate ' or use Select
    End Sub

    that way when a selection is made in the combobox it will select the specified cell!

    Re: Spreadsheet Documentation

    When hiding the other worksheets rather than turn menu functions on and off why not just hide the menu bar (command bar), however to do this you will have to display a menubar of your own first say with one button marked "Back" then when clicked does the reverse!

    Re: Run Code On Multiple Sheets

    Mustang, welcome to ozgrid!, please supply the code you have for us to be able to help you, if you can attach a workbook with dummy data showing what you would expect to happen it would be very helpful!

    Re: Lock & Unlock A Cell Based On Another Cells Data With A Formula

    Put this in the worksheet module, assuming your checkbox is checkbox1:

    Private Sub CheckBox1_Click()
    Call lock_unlock
    End Sub

    then put the code below in a standard module:

    Re: Copy Values To New Workbook Without Formula

    I don't really understand what you are trying to do but this might push you in the right direction:

    Sub copy_to_new_workbook()
    If Range("A1").Value = 1 Then ' change value to suit
    Range("A2:C10").Copy ' change range to copy to suit
    Workbooks.Add (1) 'adds a workbook using sheet 1 as template remove (1) for entire new workbook
    End If
    End Sub

    Re: Download In Excess Of 65536 Rows

    Dave i can only imagine the download is from Access or similar db or Excel 2007, whichever it is a ridiculous amount of data to try to control in one dump....surely when using xls 3.0 the user gets a warning when the data exceeds the space? i also feel i have to comment on the "server" not being able to download to any other format!, any ideas as to which app. is going to give a chunk of data, in rows, that can be manipulated by xls 3.0?

    Re: Highlight Duplicates In Range

    Conditional formatting is definately the way to go! its quick and efficient, so, highlight your entire range, then in the white box below the word FILE on the toolbar (currently showing A1) type the range name Range1, then choose FORMAT, CONDITIONAL FORMATTING, change from Cell Is to Formula Is and then enter this =IF(COUNTIF(Range1, A1)>1,TRUE,FALSE) select your format colour and your done!, all duplicates will now show up!

    Re: Conditional Macro To Print Worksheets

    Perhaps something like this:

    Sub Print_Out_Sheets()
    If Sheets(1).Range("A1").Value = "" Then
    ElseIf Sheets(1).Range("A1").Value = "1" Then
    Sheets(1 & 2).PrintOut
    ElseIf Sheets("Sheet1").Range("A1").Value = "2" Then
    Sheets(1 & 2 & 3).PrintOut
    End If
    End Sub

    Not Tested!

    Re: Blank Textbox Causes Type Mismatch Error In Equation

    How about setting the value of the box in the event of a blank i.e

    If TextBox1.Value="" Then TextBox1.value =0

    or something of that nature?

    Added by admin;

    If IsEmpty(TextBox1) Then

    Re: Prevent Empty Cell With Data Validation

    As far as i know you cannot select a cell with formula, i know you said you have a VBA solution but here's another!
    Option Explicit

    other than that you could use the sheet properties to only allow unlocked cells to be selected!

    Added by admin

    See Prevent Blanks in an Excel Table/List far from foolproof though.