Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

Public Sub, Empty Cell

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

Open in new window

_____________________________________________


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

Open in new window

Private Sub TextBox1_Change()
     CellLocation
     wsRoll.Cells(c, 7).Value = TextBox1.Value
UpdateSheet
End Sub

Open in new window

0
AaronBanker
Asked:
AaronBanker
  • 3
  • 2
1 Solution
 
QlemoC++ DeveloperCommented:
You should use Select Case in your sub ;-).

The reason it does not work that way is because you define c inside of the sub, that makes it local to it, and dismissed after the sub has been called. Writing CellLocation as a function is a better idea:
Public Function CellLocation() As Integer
  Select Case wsMenu.Cells(12, 3).Value
    Case "081": CellLocation = 2
    Case "082": CellLocation = 494
    Case "083": CellLocation = 986
    Case "084": CellLocation = 1478
    Case "085": CellLocation = 1970
    Case "086": CellLocation = 2462
    Case "087": CellLocation = 2954
    Case "099": CellLocation = 3446
  End Select
End Function

Private Sub TextBox1_Change()
  wsRoll.Cells(CellLocation(), 7).Value = TextBox1.Value
  UpdateSheet
End Sub

Open in new window

0
 
AaronBankerAuthor Commented:
GrahamSkan:  I have no idea what "Code snippets in the question are now in code boxes." means
0
 
AaronBankerAuthor Commented:
Qlemo:

would the next textbox be


Private Sub TextBox2_Change()
  wsRoll.Cells(CellLocation()+1, 7).Value = TextBox1.Value
  UpdateSheet
End Sub

Private Sub TextBox2_Change()
  wsRoll.Cells(CellLocation()+2, 7).Value = TextBox1.Value
  UpdateSheet
End Sub
0
 
QlemoC++ DeveloperCommented:
I cannot tell whether your code is correct for what you are after, but syntactically it would work that way - if you name the subs correctly (the last sub needs to be TextBox3_Change()). And I suppose you want to change Textbox1.value to the respective one.
0
 
AaronBankerAuthor Commented:
this doesn't seem to work
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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