Posts by Simon Lloyd

  • Refresh All PivotTable In Workbook

    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

    Code
    If Target.Value > 0 Then

    type Call Refresh_PivotTables



    Added by admin


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

  • Select Next Control / Cell On Worksheet After Enter In Control

    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

    Code
    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!

  • Spreadsheet Documentation

    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!

  • Run Code On Multiple Sheets

    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!

  • Lock & Unlock A Cell Based On Another Cells Value

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


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

    Code
    Private Sub CheckBox1_Click()
Call lock_unlock
End Sub


    then put the code below in a standard module:

  • Copy Range To New Workbook Based On Cell Value

    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:

    Code
    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
ActiveSheet.Paste
End If
End Sub

  • Download In Excess Of 65536 Rows

    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?

  • Highlight Duplicates In Range

    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!

  • Conditional Macro To Print Worksheets

    Re: Conditional Macro To Print Worksheets


    Perhaps something like this:

    Code
    Sub Print_Out_Sheets()
If Sheets(1).Range("A1").Value = "" Then
Sheets(1).PrintOut
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!

  • Determine If Textbox Is Empty

    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

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

    or something of that nature?


    Added by admin;


    Code
    If IsEmpty(TextBox1) Then

  • Prevent Empty Cell With Data Validation

    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.