Couple of VBA Questions

[Sean Stuber]

I have three questions:

1. How would I search every sheet for a value, and if that value exists,
delete that sheet. then continue checking other sheets.

For Each Sh In Worksheets
If Range("OrderStatus").Cells(1, 1).Value = 2 Then
ActiveSheet.Delete
End If
Next

2. How do you hide the column/row ABC 123 headings using VBA?

3. Is there a trick to getting user specified text to write into a
merged cell? My code is below but doesn't work ie. no words show up in
the merged cell.

Range("C1518").Select
ActiveCell.FormulaR1C1 = InputBox(prompt:="Enter Additional Comments")

Thanks a lot. You guys rock.

 

[Frank Kabel]

Hi Sean
1. (not tested)
dim sh as worksheet
For Each Sh In Worksheets
If Range("A1").Value = 2 Then
Sh.Delete
End If
Next

2. ActiveWindow.DisplayHeadings = False

3. Try
Dim ret_value
ret_value = InputBox(prompt:="Enter Additional Comments")
Range("C15").value = ret_value

 

[Sean Stuber]

I tried this code but it would not write to the merged cell C1518.
Any ideas?

Dim ret_value
ret_value = InputBox(prompt:="Enter Additional Comments")
Range("C15").Value = ret_value

Also the code below does not seem to work ie. delete a sheet if a
specified cell has a value of 2. Any other ideas?

Dim sh As Worksheet
For Each sh In Worksheets
If Range("OrderStatus").Cells(1, 1).Value = 2 Then
sh.Delete
End If
Next

Thanks.

 

[Dave Peterson]

Small correction to Frank's code for #1:

Option Explicit
Sub testme()
Dim sh As Worksheet
Application.DisplayAlerts = False
For Each sh In Worksheets
If sh.Range("A1").Value = 2 Then
If Worksheets.Count > 1 Then
sh.Delete
End If
End If
Next sh
Application.DisplayAlerts = True
End Sub

I qualified the range (sh.range("a1")) and checked to see if it was the last
sheet left and added the displayalerts to stop the prompts.

And if you have any hidden sheets, then this code could still blow up (at least
one visible sheet must exist in the workbook). You can add a check for that if
it's important.