• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 91
  • 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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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