2

I am trying to write a macro that looks at a cell and tells me if it's empty, and if it is, open up an input box to fill in the cell. The below code is not opening the message box regardless if the cell is empty or not. C37 IS A MERGED CELL which I know could be mucking things up. What am I missing here? 

If IsEmpty("C37") Then


Dim SIGN As String          'Sign-off

SIGN = InputBox("Enter Your Name:", "Dispositioned By:")

Range("C37").Value = SIGN
Range("G37").Value = Format(Now(), "MM/DD/YYYY")

Else

Range("B26").Select

End If

UPDATE:

With ActiveSheet

 If IsEmpty(ActiveWorkbook.ActiveSheet.Range("C23")) Then

 Dim SIGN As String          'Sign-off

 SIGN = InputBox("Enter Your Name:", "Dispositioned By:")

 Range("C23").Value = SIGN
 Range("G23").Value = Format(Now(), "MM/DD/YYYY")

 Else

 Range("B13").Select

 End If
 End With

Is the new code I've ended up with. This is not opening the inputbox even when the cell is "empty"

Is there a different phrase I should be using than IsEmpty?

ladymrt
  • 85
  • 3
  • 16
  • Watch out for implicit `ActiveSheet` references: unqualified `Range` calls implicitly refer to the active worksheet - this is the reason behind several hundreds of Stack Overflow questions. Always qualify `Range`, `Rows`, `Columns`, `Names` and `Cells` calls with an explicit `Worksheet` object. – Mathieu Guindon Apr 05 '17 at 16:49

3 Answers3

3
 If IsEmpty(ThisWorkbook.Worksheets("Sheet1").Range("C37"))

The issue is you did not tell the IsEmpty function to check for a range. You told it to check for "C37" which is a string literal and not a range object.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
Doug Coats
  • 6,255
  • 9
  • 27
  • 49
  • never mind I added the workbook and sheet and it worked. THANKS – ladymrt Apr 05 '17 at 16:38
  • The `Sheets` collection can contain `Chart` objects. When retrieving a `Worksheet` you should always use the `Worksheets` collection, otherwise you're making an implicit assumption. Also, OP didn't "not fully qualify where the range is", OP was testing whether a string literal was empty! Edit your answer and I'll upvote it and delete mine =) – Mathieu Guindon Apr 05 '17 at 16:42
  • @Mat'sMug fair enough. That's something Ill keep in mind. I keep forgetting to leave strategic typos for you <3 – Doug Coats Apr 05 '17 at 16:43
  • @Mat'sMug I edited my answer to be more factual. Thanks for keeping me in check - i do learn quite a bit form you :) – Doug Coats Apr 05 '17 at 16:46
  • 2
    While it is generally not considered 'best practice' to inherit a parent worksheet, that *could* have been written as `If IsEmpty([C37]) Then` but better as above or like `If IsEmpty([Sheet1!C37]) Then`, the latter inferring the parent workbook as the ActiveWorbook. –  Apr 05 '17 at 16:51
  • @Jeeped indeed! Square bracket expressions are late-bound (slower) calls with no compile-time checks though. – Mathieu Guindon Apr 05 '17 at 16:55
  • Agreed @Mat'sMug, just throwing other possibilities out there. I never use them myself. –  Apr 05 '17 at 16:57
  • 1
    '*... which is a string literal and not a range object.*' and **never** 'empty'! –  Apr 05 '17 at 16:58
0

Okay. I've solved my problem.

I have a button that resets this form to clear contents of the cells and start over.

Unfortunately, I couldn't find a way to easily clear contents of a merged cell, so instead, I used 

Range("MERGEDCELL").Value = " "

to essentially make the cell blank. HOWEVER, my IsEmpty is not going to see those cells as empty because they have a space in them, thus why when it looks empty to me (but it's not) the InputBox doesn't pop up...

I've looked around for alternatives to clear contents of merged cell with little to no success. If anyone has suggestions in that area, let me know. Thanks again for everything everyone.

ladymrt
  • 85
  • 3
  • 16
-1

If IsEmpty(Range( "C37" )) = True Then

SIGN = InputBox( "Enter Your Name:")

Range( "C37" ).Value = SIGN

Else

Your code

Rajesh Sinha
  • 197
  • 3
  • 8