• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

VB help

Please can you help with the code below. I want to add a condition in that if offset 16 (Column R) is not equal to blank ("") then command button 12 will disable with msg box "Already entered".



Private Sub CommandButton12_Click()
   
    With rngStart
         .Offset(, 0).Value = TxtOpen
         .Offset(, 2).Value = TxtClose
         .Offset(, 16).Value = TxtNumber
             
         
    End With
     Unload Me

End Sub
help1.xls
0
Kiwi-123
Asked:
Kiwi-123
  • 4
  • 2
1 Solution
 
nutschCommented:
How do you define rngStart?

Thomas

Private Sub CommandButton12_Click()
   
if len(cells(5,18))>0 then
msgbox "Already entered"
Exit sub
end if

    With rngStart
         .Offset(, 0).Value = TxtOpen
         .Offset(, 2).Value = TxtClose
         .Offset(, 16).Value = TxtNumber
             
         
    End With
     Unload Me

End Sub

Open in new window

0
 
Kiwi-123Author Commented:
I'm sorry I'm not sure what you mean. I think rng start is:

 Set rngStart = Cells(ActiveCell.Row, "B")

So any cell in from column B
0
 
Kiwi-123Author Commented:
I was having a go at tweaking your code and thought that it may be easier to stop the userform from showing if a date was already entered.

Private Sub CommandButton2_Click()
Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart
   If Len(Cells(5, 18)) > 0 Then
MsgBox "Already entered"
Exit Sub
End If
         
     UserForm1.Show

End Sub

The only problem I get is that the msgbox comes back on every line and not just when column R is blank.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kiwi-123Author Commented:
Almost there its just the code seems to be working the opposite way? i.e locked upon no date and unlocked with.

Private Sub CommandButton2_Click()
Set rngStart = Cells(ActiveCell.Row, "B")
    With rngStart
   If .Offset(, 16).Value = "" Then
MsgBox "Already entered"
Exit Sub
End If
         
     UserForm1.Show
End With
End Sub
0
 
Kiwi-123Author Commented:
Thanks for your help, you pointed me in the right direction.
0
 
nutschCommented:
Glad to help. Sorry about missing your questions, I've only seen the notification e-mails when you accepted the solution.

Thomas
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now