Input Box only if a cell is blank

  • Thread starter Thread starter Audrey
  • Start date Start date
A

Audrey

Hi, I'm a VBA noob. At most I glean from the genius on this forum and copy
and paste your functions into my workbooks. I took a modified version of
this code directly from this forum, but I don't know how to change it so that
the input box will only pop up if the cell is blank.

Option Explicit
Private Sub Workbook_Open()

Dim ClientName As String

ClientName = InputBox("Please input the Client's Name")

Worksheets("Summary").Range("A4") = ClientName

End Sub

Also I only need the input box to pop up if the user is on the tab titled
"Summary," not as the book is opened.

Any help is greatly appreciated!

Thanks!
 
hi
if statemate......
Option Explicit
Private Sub Workbook_Open()
If WorkSheets("Summary").Range("A4").value = "" then
Dim ClientName As String
ClientName = InputBox("Please input the Client's Name")
Worksheets("Summary").Range("A4").value = ClientName
end if
End Sub

regards
FSt1
 
You need to capture 2 events. If the sheet is opened on the summary tab then
you want to add the client name... The other is if you select the summary
tab... If it were me I would write a sub to add the name (if appropriate) and
then just call it from the on open event and from the sheet activate event...
Something like this...

Option Explicit

Private Sub Workbook_Open()
Call AddClient(ActiveSheet)
End Sub

Private Sub Workbook_SheetActivate(ByVal sh As Object)
Call AddClient(sh)
End Sub

Private Sub AddClient(ByVal sh As Worksheet)
With sh
If .Name = "Summary" Then
If Trim(.Range("A4").Value) = "" Then
.Range("A4").Value = InputBox("Please input the Client's Name")
End If
End If
End With
End Sub
 
Delete the existing code from Thisworkbook module.

Go to "Summary" sheet and right-click on the sheet tab.

Copy/paste this code into that sheet module. Will run when the sheet is
activated/selected.

If A4 is blank the InputBox will appear. Otherwise.....nothing.

Private Sub Worksheet_Activate()
Dim ClientName As String
If Me.Range("A4") = "" Then
ClientName = InputBox("Please input the Client's Name")
Me.Range("A4") = ClientName
End If
End Sub


Gord Dibben MS Excel MVP
 
Option Explicit
Private Sub Workbook_Open()

Dim ClientName As String
Dim myCell as range

set myCell = me.worksheets("summary").range("a4")

if trim(mycell.value) = "" then
ClientName = InputBox("Please input the Client's Name")
mycell.value = ClientName
end if

End Sub
 
I'm not sure why it would make a difference what sheet is visible when it's
opened, but you could do something like:

Option Explicit
Private Sub Workbook_Open()

Dim ClientName As String
Dim myCell as range

set myCell = me.worksheets("summary").range("a4")

if trim(mycell.value) = "" then
application.goto mycell, scroll:=true
ClientName = InputBox("Please input the Client's Name")
mycell.value = ClientName
end if

End Sub

I missed the last part of your question.
 
Jim, this is fantastic! It's working exactly how I need it to.

Jim, Dave, Gord, FSt1, thank you all so much for your help! I really
appreciate it!!
 
Back
Top