Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

VBA Excel 2007 Require Entry in Cell B2 if isblank(A2)=false


  • Please log in to reply
2 replies to this topic

#1 Murtle the Turtle

Murtle the Turtle

  • Members
  • 4 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:Detroit Metro Area
  • Local time:11:26 PM

Posted 16 June 2011 - 08:06 AM

I am trying to save myself lots of time calling my reps to ask them to fill in the missing information from monthly reporting. I have conditional formatting to highlight the fields, but they still ignore them. I would like to set it so they can't save or close the file if any row within the table has data in column A, but column B is blank. My idea is to run VBA code on save and on close that tells them they cannot save or close until they fill in the highlighted cells. I have no idea how to make this happen. I am familiar with VBA, but no expert for sure. Any help is appreciated. Thank you very much.

BC AdBot (Login to Remove)

 


#2 Murtle the Turtle

Murtle the Turtle
  • Topic Starter

  • Members
  • 4 posts
  • OFFLINE
  •  
  • Gender:Female
  • Location:Detroit Metro Area
  • Local time:11:26 PM

Posted 17 June 2011 - 01:17 PM

An easier option would be to check the cell color because the conditional formatting colors the cells if they need to be filled in. If any cells within the given range are yellow, then they get a message telling them they can't save the file until they fill in the yellow cells. Any guidance you can provide on how to accomplish this would be greatly appreciated. Thanks.

#3 strolln

strolln

  • Members
  • 400 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:N. Calif.
  • Local time:08:26 PM

Posted 17 June 2011 - 02:14 PM

I think you would need to do it via VBA macro. Something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   If Sheet1.Cells(1, 2) = Empty Or Sheet1.Cells(1, 2) = "" Then

      Cancel = True
      MsgBox "Required field missing"
      Sheet1.Cells(1, 2).Select

   End If

End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   If Sheet1.Cells(1, 2) = Empty Or Sheet1.Cells(1, 2) = "" Then

      Cancel = True
      MsgBox "Required field missing"
      Sheet1.Cells(1, 2).Select

   End If
   
End Sub

These macros only check the cell A2 so if you need to check a range you'd need to modify them.

Edited by strolln, 17 June 2011 - 02:16 PM.

To Err is human; to really foul things up requires a Bleeping Computer!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users