troubleshooting Question

Public Sub, Empty Cell

Avatar of AaronBanker
AaronBanker asked on
Microsoft ExcelVisual Basic Classic
5 Comments1 Solution133 ViewsLast Modified:
I have many textboxes to change data.  the first Sub below was working, but with a few hundred textboxes this code slowed down the program.  So I made a public sum, as seen below, but i kept getting the value of c = "Empty"  

what am i doing wrong?

Private Sub TextBox1_Change()
   
     If wsMenu.Cells(12, 3).Value = "081" Then wsRoll.Cells(2, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "082" Then wsRoll.Cells(494, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "083" Then wsRoll.Cells(986, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "084" Then wsRoll.Cells(1478, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "085" Then wsRoll.Cells(1970, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "086" Then wsRoll.Cells(2462, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "087" Then wsRoll.Cells(2954, 7) = TextBox1.Value
     If wsMenu.Cells(12, 3).Value = "099" Then wsRoll.Cells(3446, 7) = TextBox1.Value
    
End Sub
_____________________________________________


Public Sub CellLocation()

    Dim c As Integer
    
     If wsMenu.Cells(12, 3).Value = "081" Then c = 2
     If wsMenu.Cells(12, 3).Value = "082" Then c = 494
     If wsMenu.Cells(12, 3).Value = "083" Then c = 986
     If wsMenu.Cells(12, 3).Value = "084" Then c = 1478
     If wsMenu.Cells(12, 3).Value = "085" Then c = 1970
     If wsMenu.Cells(12, 3).Value = "086" Then c = 2462
     If wsMenu.Cells(12, 3).Value = "087" Then c = 2954
     If wsMenu.Cells(12, 3).Value = "099" Then c = 3446

End Sub
Private Sub TextBox1_Change()
     CellLocation
     wsRoll.Cells(c, 7).Value = TextBox1.Value
UpdateSheet
End Sub
ASKER CERTIFIED SOLUTION
Qlemo
"Batchelor", Developer and EE Topic Advisor

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Top Expert 2015

The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.

Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros