Input Box only if a cell is blank

[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!

 

[FSt1]

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

 

[Jim Thomlinson]

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

 

[Gord Dibben]

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

 

[Jim Thomlinson]

What if the book opens with the summary sheet as the active sheet?

 

[Dave Peterson]

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

 

[Dave Peterson]

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.

 

[Audrey]

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!!