Posts by Cetric

Re: Before_close Event Infinite Loop

Let me give that a try. Sorry for the delay, it was near the end of the day for me..Thanks!

I have a workbook where I want to verify data put into it before the work book is closed. I have a working Before_close event setup to check data in the workbook...but I can't seem to break out of it after the check is done. I had thought that a module level boolean would allow me an external loop control, but it doesn't seem to be working.

The format I enivision is such:

Before_close

Check data

if data is good
Close workbook
else
Show error message

End

I've stepped through the execution and everytime it gets to the CLOSE WORKbook section, it basically starts over. How do I get it to check the data and if the data is good, only run the one time and close?

This is how it starts:

m_blnCloseWorkbook as Boolean 'I thought this, being outside the sub, 
'would allow me to control it.


Private Sub Workbook_BeforeClose(Cancel As Boolean)


' varius dim statements


If m_blnCloseWorkbook = False Then
    Cancel = True
Else
    Cancel = False
    Application.Quit
End If


'Rest of code to check the data and show the error message 
'if any errors found

Later, I have

If iErrorCount > 1 Then
    wkbTarget.Sheets("UncheckedCombustors").Visible = True
    wkbTarget.Sheets("UncheckedCombustors").Select
    m_blnCloseWorkbook = False
Else
NotTimeYet:
    m_blnCloseWorkbook = True
    wkbTarget.Sheets("Conversion Form").Select
    wkbTarget.Save
    wkbTarget.Close 'I'm not sure if I need this line or not.
End If

I had thought this would run once, if the data was good, then
close, but it just loops.

Re: Make a hyperlinked checklist on the fly

Works perfectly! Just the way I imagined. Now, having seen this, I can answer your question about which cell to link to, which would be the cell address of the value pulled into the sCombusterChecked variable. I'm sure I can handle that, though. Thanks for getting me started!

I have a workbook with several worksheets in it. I have VBA code that checks to see if certain cells on some of those sheets are filled in. If those specific cells are empty, my code creates a list on a pre-designed, normally hidden, hidden spreadsheet of those Sheets that need to have their information filled in.

I have multiple copies of this workbook, each specific to the end-user, but making the same checks. My end-users have wildly varying levels of technical ability. One idea I had to make this workbook more user friendly was to hyperlink that list of sheets that need to be double checked, so the user could click on the name of the sheet and go directly to it, rather than having to scroll through 100 tabs and find the one he needs.

Here is my code to create the list; I imagine my hyperlinking would live in the same place.

--Other sections of code--

Else
                sSheetName = ActiveSheet.Name
                sWellName = ActiveSheet.Range("K1").Text
                sCombustorExists = ActiveSheet.Range("AH4").Text
                sCombustorChecked = WorksheetFunction.VLookup(iReportDate, ActiveSheet.Range("B7:AI37"), 33)
                sCombustorRelit = WorksheetFunction.VLookup(iReportDate, ActiveSheet.Range("B7:AI37"), 34)
                
                If sCombustorExists = "FALSE" Then
                    GoTo NextSheet
                Else
                    If sCombustorChecked = "Yes" Or sCombustorRelit = "Yes" Then
                        GoTo NextSheet
                    Else
                        With wkbTarget.Sheets("UncheckedCombustors")
                            .Unprotect
                            .Cells(iErrorCount + 1, 1).Value = sSheetName
                            .Cells(iErrorCount + 1, 2).Value = sWellName
                            iErrorCount = iErrorCount + 1
                            .Protect
                        End With
                    End If
                End If

--the rest of the code--

Re: Looking for a single function to change the captions for numerous toggle buttons

Largely because I could change the size of the toggle button, but I was less successful at making the checkbox or radio button appear on the sheet at a size I wanted. The fill is too big to attach, but I mocked a version of it to show you. The B's indicate where buttons are intended to go.

Re: Looking for a single function to change the captions for numerous toggle buttons

My apologies. The buttons are controls on a spreadsheet, indicating whether or not a piece of equipment has been checked on a given day of the month. I put toggle buttons to make life a little easier for the intended users.

I have 30 buttons that I want to toggle between YES and NO when pressed. Is there a single code to apply to each button to accomplish this, or must I continue copying and modifying the same code for button as I have been? I keep thinking there must be an easier way, but it is escaping me at the moment.

Re: Option Box/ Checked Box

Validation is built into Excel. You can find it in Excel under the Data Menu.

You can use it to restrict what sort of responses users can enter on your
sheet. What exactly did you have in mind for your project?

Re: Summary Report using data from different sheets

I don't know if I am reading your message right, but I threw together a small sheet to show you how I would have tackled your situation.

The Summary 1 sheet shows a table of collected data from the other 5 sheets. The Summary 2 sheet lets you pick a date to display data you choose.

Did you need something different?

Re: Selecting specific Sheets in a workbook based on the contents of a cell in each sheet

I toyed with the code a bit, and I managed to get it to work. Here's what I came up with:

Sub Select_And_Unprotect_Only_Well_Sheets()
     
    Dim wbk As Workbook
    Dim ws As Worksheet
    Dim blnReplace As Boolean
     
    blnReplace = True
    Sheets(1).Select
    For Each ws In ActiveWorkbook.Worksheets
        If (IsNumeric(ws.Range("a1").Value)) Then
            ws.Unprotect
            ws.Select blnReplace
            blnReplace = False
        End If
    Next ws
     
End Sub

Thanks for your help!

Re: Selecting specific Sheets in a workbook based on the contents of a cell in each sheet

No, it doesn't appear to have selected any sheets before the error.

Re: Selecting specific Sheets in a workbook based on the contents of a cell in each sheet

Thanks! That worked on the base form. Unfortunately, when I copied it to a module in one of my working files, I got a run-time error when I tried it. The message was:

Method 'Select' of object '_Worksheet' failed.

It occurred on this line:

ws.Select blnReplace

What does the blnReplace do anyway?

I have a spreadsheet, attached, which contains a number of different types of sheets. This is the base sheet, from which the working sheets will be spawned. The tab names identify the different types of sheets. In the working file, there will likely be over 100 sheets in this workbook, made up of different combinations of the sheets that are shown in this file. Two of those types, the Guage and the CBM sheets, will have unique id numbers in cell A1. The other sheets will not. I am trying to develop some VB that will select in a group all the sheets that have id numbers, ignoring the rest. I am thinking I want to accomplish this by way of a FOR EACH Sheet in Workbook style loop, using a check for the id number in A1, and selecting those sheets.

Sub Select_And_Unprotect_Only_Well_Sheets()


    Dim wbk As Workbook
    Dim ws As Worksheet
    
    For Each ws In wbk.Worksheets
        If (IsNumeric(ws.Range("a1").Value)) Then
            ws.Select
            ws.Unprotect
        End If
     Next ws
     
End Sub

Re: VBA Cell Value Change

Is the time portion always the same?

Re: Restrict display of VB calculation steps

Much more elgant than mine. I knew there was a better way to write this!

What's more, it works just as I desired! Many thanks to you, Norie, for the help and the lesson!

Re: Restrict display of VB calculation steps

I hope this isn't too long.

Sub Auto_Open()
'This code updates the spreadsheet from the component files refrenced on the
'Data Pull Sheet


    Dim i As Integer
    Dim t As Single
    
    'Unprotect the workbook before updating.
    Worksheets(1).Select
    With ActiveSheet
        .Unprotect
    End With
    
    Worksheets(2).Select
    With ActiveSheet
        .Unprotect
    End With
    
    'establish time the file was opened
    Set t = Range("b1").Text
    
    'change the status bar to let you know what's going on
    Application.StatusBar = "Updating values.  Please stand by."
    
    'Prevent changes from showing on the screen
    Application.ScreenUpdating = False


    'If the file is opened before 1045, then yesterday's data hasn't come in yet, so skip
    'updating those fields, but update everything else.  If it's after 1045, update everything
    Select Case t
        Case t >= 1045
            'Update the form if the current time is after 1045
            'For i = 3 To 191 Step 2 'My code
            For i = 4 To 202 Step 2 'Mark's Code
                Range("W" & i).Select
                'Range("V" & i).Select
                'Selection.Copy
                'ActiveCell.Offset(1, 1).Select
                'ActiveCell.ClearContents
                'Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                    'SkipBlanks:=False, Transpose:=False
                ActiveCell.Formula = ActiveCell.Offset(0, -1).Text
                ActiveCell.Formula = ActiveCell.Text
                ActiveCell.Parse
              Next
        Case t <= 1045
            'Update only from files prior to yesterday
            For i = 44 To 202 Step 2
                Range("W" & i).Select
                ActiveCell.Formula = ActiveCell.Offset(0, -1).Text
                ActiveCell.Formula = ActiveCell.Text
                ActiveCell.Parse
              Next
    End Select
    
    'Restore the default status bar
    Application.StatusBar = False


    'Protect the workbook after updating
    Worksheets(1).Select
    With ActiveSheet
       .Protect
    End With
    
    Worksheets(2).Select
    With ActiveSheet
       .Protect
    End With
    
    'Return focus to the main page
    Worksheets(1).Select
    Range("A1").Select
    'Allow changes to show on the screen again
    Application.ScreenUpdating = True


End Sub

Re: Restrict display of VB calculation steps

Thanks for the tip!

I've tried both of them already, however.

The Screenupdating is working fine - the screen flips to the Data sheet, and you don't have to watch the sequence of steps of the code.

The hidden sheet generated an error when I used it. When the file opens, it shows the Values sheet, because that's what the higher ups want to see. To update the values, I have code selecting the Data sheet, and when I hide the Data sheet, that select method fails.

Good morning!

I have a workbook with 2 sheets in it: (1) Values and (2)Data.

I have code in the Data sheet to pull specific data from a range of other files.

The Values sheet is the accumulation of the information in the Data sheet.

Put another way, VB code runs in the Data sheet, and the Values sheet is a summary of the results.

This form is meant to display the Values to the higher ups. Currently, upon opening the Workbook, and triggering the Auto_Open code, you see the Values sheet, then flip to the Data sheet while the code updates the data, then flips back to the Values sheet. I would prefer all the processing be completely hidden. How can I modify the code to keep the Values sheet visible and perform the updates in the background?