Automate Responses to Message Boxes and Input Boxes

[AJS]

Hi,

I would like to be able to automate the responses to message boxes and imput boxes in some of my subroutines, depending on the input from other subroutines. Is it possible to do this?

A fragment of one such subroutine I would like to automate occasionally is below:

Sub ChangeDim()
' Change one of the dimensions to be measured
Dim Show_Box As Boolean
Dim Wks As Worksheet
Dim DimRep As Variant
Sheet1.Protect UserInterfaceOnly:=True
ans = MsgBox("Are you sure you want to proceed?", vbOKCancel + vbExclamation)
        If ans = vbCancel Then Exit Sub
' Name and verify the dimension abbreviation to be replaced
Show_Box = True
While Show_Box = True
DimRep = InputBox("Enter the abbreviation of the dimension you wish to change", "Replaced dimension")
' Test abbreviation to find out if it is unique
For Each Wks In Worksheets
        If StrComp(Wks.Name, DimRep, 1) = 0 Then
             Show_Box = False
             Exit For
        End If
    Next Wks
If Show_Box = True Then MsgBox "Abbreviation not found!"
Wend
Sheet1.Cells(6 + Sheets(DimRep).Index, 5) = DimRep
end Sub

[TonyJollans]

It's late, I must be missing something.

I don't understand what you want to do with the code, but you say you want to automate responses to your own prompts. Whatever the criteria are, can you not just use them to not issue the prompts in the first place.

[AJS]

Yeah, probably could, but would require substantial recoding. Just feeling a bit lazy I guess, and after a quick fix. Oh well - have started rewriting...

[Jacob Hilderbrand]

You can do something like this:

If something Then
    ans = vbYes
Else
    ans = MsgBox("Are you sure you want to proceed?", vbOKCancel + vbExclamation) 
End If

[AJS]

Yeah, that's what I've started using, with a public boolean.

Basically, I've started setting my sheet up so that values can be entered in manually or imported from another sheet (hence wanting to supress the message boxes and input boxes). However, after much coding that went okay I've started getting a "select method of range class failed" error. I guess this error has something to do with the fact I'm copying and pasting between 2 different workbooks? This started after I set up a dynamic array for such copying, although in a different subroutine whixh previously seemed to be error-free...

[Jacob Hilderbrand]

You don't need to select to copy and paste. Post your code and maybe we can fix it up.

[AJS]

The offending code fragment is below, and this portion just sets up some formatting. "ImpShots" is an integer retrieved from the imported data, although this portion of the code works in the control workbook (that book to which the data is being imported):

Sheet1.Range("C" & 11 + ImpShots & ":C19").Select
    With Selection
        .Clear
        .Interior.ColorIndex = 15
        .Borders(xlEdgeLeft).LineStyle = xlNone
        .Borders(xlEdgeRight).LineStyle = xlNone
        .Borders(xlEdgeTop).LineStyle = xlNone
        .Borders(xlEdgeBottom).LineStyle = xlNone
        .Borders(xlInsideHorizontal).LineStyle = xlNone
    End With

Another problem with the importing - It is set up so that apart from Sheet1 on the control borkbook, all other sheets are replaced by those from the imported workbook. If there are less sheets in the imported workbook, the last one(s) in the control workbook will be removed, If there are more sheets in the imported workbook, one or more sheets will be added to the control workbook. Sometimes the sheet numbers become noncontinuous after this process - for example, while I may have had sheet1, sheet2, sheet3, sheet4 in the control workbook to begin with, after importing I now have sheet1, sheet2, sheet3, sheet4, sheet 7, sheet8 (ie, 7 and 8 instead of 5 and 6). This leads to problems when referring to these worksheets later on. Any way I can force the sheet numbers to become continuous?

[Jacob Hilderbrand]

You could reference the sheets by index and that would ignore their names.

For i = 1 to Sheets.Count 
Sheets(i). 'Do something
Next i

[Ivan F Moala]

If you have Chart sheets then don't use the sheets Index method.
Iterate through them using the [For Each] [Next] construct as a WorkSheet and Not Sheet
as in

Dim Wsh As Worksheet
For Each Wsh In ActiveWorkbook.Worksheets
    Wsh. 'do something
Next

[AJS]

Thanks,

In reply to DRJ, I was already using the Sheets(i) reference method but had misunderstood how it worked until recently (ie tab order rather than sheet number), so all I had to do was lock the worksheet order in my workbook and the method works fine.

In reply to Ivan, my charts are imbedded in worksheets rather than as seperate sheets, and the problem had more to do with importing data between workbooks.

In the end, I found the simplest solution was to just delete all worksheets bar Sheet1 from the first workbook, and then to copy all worksheets bar Sheet1 from the second workbook to the first - much easier to code!

For i = 1 To CWBDim
    CurrWB.Sheets(2).Delete
Next i
For i = 1 To ExpWB.Worksheets.Count - 1
    ExpWB.Sheets(i + 1).Copy after:=CurrWB.Sheets(i)
Next i

where CWBDim = CurrWB.Worksheets.Count - 1 before deleting any of the sheets.