assigning a value to a variable

P

Phillip2

Board Regular
Joined
Aug 5, 2019
Messages
79
Office Version
  1. 365
Platform
  1. Windows
I have a worksheet with 100 buttons. Each button represents a Customer’s record number. I’m working on a macro to assign to each button that when clicked would do the following:
  • Open the userform CCX_Form
  • Assign the customer’s record number to the variable TBrecord
  • Run the subroutine Private Sub Search_Click()
My form opens, but I’m getting an error 424 (Object Required) when using the following code. What am I missing?

VBA Code: 
Sub Rectangle1_Click()
CCX_Form.Show
TBrecord.Value = "001"
Call Sub Search
End Sub

Thank you in advance for your time and help.
 
Sort by date Sort by votes
See picture
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    22.3 KB · Views: 6
Upvote 0

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Thanks, but that doesn't show how you tried to run the Search_Click
 
Upvote 0
I’m not sure just what you want. I thought that you asked how I put it inside of the initialize event.

When I clicked on my button, the sub changed my variable and opened the form, but the code that I added to the initialize event didn’t call the sub. I didn’t get any errors it just didn’t work. Attached are two pictures. One is with the script, and the other is manually running the subroutine.
 

Attachments

  • with script.JPG
    with script.JPG
    65.8 KB · Views: 3
  • manually running search.JPG
    manually running search.JPG
    108.9 KB · Views: 3
Upvote 0
My apologies, I didn't notice that you had added Search_click above the Dim statements.
Add the word Stop directly above Search_Click & then click the Shape to launch the userform.
The code should stop & highlight the word Stop, you can then step through the code using F8 , does it take you into the Search_click code?
 
Upvote 0
I’m more confused than ever now. I entered the Stop and stepped through and it does go through the subroutine. It still doesn’t do anything, but it ran. Then I ran it manually and it came with an error about dimming a variable twice. Sorry, I didn’t write it down.

At this point it may be easier to just press the button to run the search routine when the form opens. It just seems like it is so close.
 
Upvote 0
Can you post the complete Initialize event code & the Search click code?
 
Upvote 0
VBA Code: 
[SIZE=2]
'___________________________Search  and Display Button____________________________________________
Private Sub Search_Click()
Dim Record As String
Record = Val(TBrecord)
LastRow = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If Worksheets("Data").Cells(i, 1).Value = Record Then
'____________Information Frame________________________
TBccxName = Worksheets("Data").Cells(i, 2).Value
TBlocation = Worksheets("Data").Cells(i, 3).Value
TBcontact = Worksheets("Data").Cells(i, 4).Value
TBemail = Worksheets("Data").Cells(i, 5).Value
TBtelephone = Worksheets("Data").Cells(i, 6).Value
'____________Triggers Frame________________________
'Triggers
TBtrigger1 = Worksheets("Data").Cells(i, 7).Value
TBtrigger2 = Worksheets("Data").Cells(i, 8).Value
TBtrigger3 = Worksheets("Data").Cells(i, 9).Value
TBtrigger4 = Worksheets("Data").Cells(i, 10).Value
TBtrigger5 = Worksheets("Data").Cells(i, 11).Value
TBtrigger6 = Worksheets("Data").Cells(i, 12).Value
TBtrigger7 = Worksheets("Data").Cells(i, 13).Value
TBtrigger8 = Worksheets("Data").Cells(i, 14).Value

'Sesssion Limits

CBsession1 = Worksheets("Data").Cells(i, 15).Value
CBsession2 = Worksheets("Data").Cells(i, 16).Value
CBsession3 = Worksheets("Data").Cells(i, 17).Value
CBsession4 = Worksheets("Data").Cells(i, 18).Value
CBsession5 = Worksheets("Data").Cells(i, 19).Value
CBsession6 = Worksheets("Data").Cells(i, 20).Value
CBsession7 = Worksheets("Data").Cells(i, 21).Value
CBsession8 = Worksheets("Data").Cells(i, 22).Value

'Call Control Groups

CBcallControl1 = Worksheets("Data").Cells(i, 23).Value
CBcallControl2 = Worksheets("Data").Cells(i, 24).Value
CBcallControl3 = Worksheets("Data").Cells(i, 25).Value
CBcallControl4 = Worksheets("Data").Cells(i, 26).Value
CBcallControl5 = Worksheets("Data").Cells(i, 27).Value
CBcallControl6 = Worksheets("Data").Cells(i, 28).Value
CBcallControl7 = Worksheets("Data").Cells(i, 29).Value
CBcallControl8 = Worksheets("Data").Cells(i, 30).Value

'____________Scripts Frame________________________

'Scripts

TBscripts1 = Worksheets("Data").Cells(i, 31).Value
TBscripts2 = Worksheets("Data").Cells(i, 32).Value
TBscripts3 = Worksheets("Data").Cells(i, 33).Value
TBscripts4 = Worksheets("Data").Cells(i, 34).Value
TBscripts5 = Worksheets("Data").Cells(i, 35).Value
TBscripts6 = Worksheets("Data").Cells(i, 36).Value
TBscripts7 = Worksheets("Data").Cells(i, 37).Value
TBscripts8 = Worksheets("Data").Cells(i, 38).Value

'____________Prompts Frame________________________

  'Prompt Types

CBprompt1 = Worksheets("Data").Cells(i, 39).Value
CBprompt2 = Worksheets("Data").Cells(i, 40).Value
CBprompt3 = Worksheets("Data").Cells(i, 41).Value
CBprompt4 = Worksheets("Data").Cells(i, 42).Value
CBprompt5 = Worksheets("Data").Cells(i, 43).Value
CBprompt6 = Worksheets("Data").Cells(i, 44).Value
CBprompt7 = Worksheets("Data").Cells(i, 45).Value
CBprompt8 = Worksheets("Data").Cells(i, 46).Value

'Open Time

CBopen1 = VBA.Format$(Worksheets("Data").Cells(i, 47).Value, "hh:Nn")
CBopen2 = VBA.Format$(Worksheets("Data").Cells(i, 48).Value, "hh:Nn")
CBopen3 = VBA.Format$(Worksheets("Data").Cells(i, 49).Value, "hh:Nn")
CBopen4 = VBA.Format$(Worksheets("Data").Cells(i, 50).Value, "hh:Nn")
CBopen5 = VBA.Format$(Worksheets("Data").Cells(i, 51).Value, "hh:Nn")
CBopen6 = VBA.Format$(Worksheets("Data").Cells(i, 52).Value, "hh:Nn")
CBopen7 = VBA.Format$(Worksheets("Data").Cells(i, 53).Value, "hh:Nn")
CBopen8 = VBA.Format$(Worksheets("Data").Cells(i, 54).Value, "hh:Nn")


'Close Time

CBclose1 = VBA.Format$(Worksheets("Data").Cells(i, 55).Value, "hh:Nn")
CBclose2 = VBA.Format$(Worksheets("Data").Cells(i, 56).Value, "hh:Nn")
CBclose3 = VBA.Format$(Worksheets("Data").Cells(i, 57).Value, "hh:Nn")
CBclose4 = VBA.Format$(Worksheets("Data").Cells(i, 58).Value, "hh:Nn")
CBclose5 = VBA.Format$(Worksheets("Data").Cells(i, 59).Value, "hh:Nn")
CBclose6 = VBA.Format$(Worksheets("Data").Cells(i, 60).Value, "hh:Nn")
CBclose7 = VBA.Format$(Worksheets("Data").Cells(i, 61).Value, "hh:Nn")
CBclose8 = VBA.Format$(Worksheets("Data").Cells(i, 62).Value, "hh:Nn")

'Wave Files

TBwave1 = Worksheets("Data").Cells(i, 63).Value
TBwave2 = Worksheets("Data").Cells(i, 64).Value
TBwave3 = Worksheets("Data").Cells(i, 65).Value
TBwave4 = Worksheets("Data").Cells(i, 66).Value
TBwave5 = Worksheets("Data").Cells(i, 67).Value
TBwave6 = Worksheets("Data").Cells(i, 68).Value
TBwave7 = Worksheets("Data").Cells(i, 69).Value
TBwave8 = Worksheets("Data").Cells(i, 70).Value

'____________Main Menu Frame________________________

'Main Menu 1 Options

TBmainMenu1 = Worksheets("Data").Cells(i, 71).Value
TBmainMenu2 = Worksheets("Data").Cells(i, 72).Value
TBmainMenu3 = Worksheets("Data").Cells(i, 73).Value
TBmainMenu4 = Worksheets("Data").Cells(i, 74).Value
TBmainMenu5 = Worksheets("Data").Cells(i, 75).Value
TBmainMenu6 = Worksheets("Data").Cells(i, 76).Value
TBmainMenu7 = Worksheets("Data").Cells(i, 77).Value
TBmainMenu8 = Worksheets("Data").Cells(i, 78).Value
TBmainMenu9 = Worksheets("Data").Cells(i, 79).Value
TBmainMenu0 = Worksheets("Data").Cells(i, 80).Value
TBmainMenuStar = Worksheets("Data").Cells(i, 81).Value
TBmainMenuPound = Worksheets("Data").Cells(i, 82).Value

'____________Sub Menu Frame________________________

'Sub Menu 1 Options

TBsubMenu11 = Worksheets("Data").Cells(i, 83).Value
TBsubMenu12 = Worksheets("Data").Cells(i, 84).Value
TBsubMenu13 = Worksheets("Data").Cells(i, 85).Value
TBsubMenu14 = Worksheets("Data").Cells(i, 86).Value
TBsubMenu15 = Worksheets("Data").Cells(i, 87).Value
TBsubMenu16 = Worksheets("Data").Cells(i, 88).Value
TBsubMenu17 = Worksheets("Data").Cells(i, 89).Value
TBsubMenu18 = Worksheets("Data").Cells(i, 90).Value
TBsubMenu19 = Worksheets("Data").Cells(i, 91).Value
TBsubMenu10 = Worksheets("Data").Cells(i, 92).Value
TBsubMenu1Star = Worksheets("Data").Cells(i, 93).Value
TBsubMenu1Pound = Worksheets("Data").Cells(i, 94).Value

'Sub Menu 2 Options

TBsubMenu21 = Worksheets("Data").Cells(i, 95).Value
TBsubMenu22 = Worksheets("Data").Cells(i, 96).Value
TBsubMenu23 = Worksheets("Data").Cells(i, 97).Value
TBsubMenu24 = Worksheets("Data").Cells(i, 98).Value
TBsubMenu25 = Worksheets("Data").Cells(i, 99).Value
TBsubMenu26 = Worksheets("Data").Cells(i, 100).Value
TBsubMenu27 = Worksheets("Data").Cells(i, 101).Value
TBsubMenu28 = Worksheets("Data").Cells(i, 102).Value
TBsubMenu29 = Worksheets("Data").Cells(i, 103).Value
TBsubMenu20 = Worksheets("Data").Cells(i, 104).Value
TBsubMenu2Star = Worksheets("Data").Cells(i, 105).Value
TBsubMenu2Pound = Worksheets("Data").Cells(i, 106).Value

'Sub Menu 3 Options

TBsubMenu31 = Worksheets("Data").Cells(i, 107).Value
TBsubMenu32 = Worksheets("Data").Cells(i, 108).Value
TBsubMenu33 = Worksheets("Data").Cells(i, 109).Value
TBsubMenu34 = Worksheets("Data").Cells(i, 110).Value
TBsubMenu35 = Worksheets("Data").Cells(i, 111).Value
TBsubMenu36 = Worksheets("Data").Cells(i, 112).Value
TBsubMenu37 = Worksheets("Data").Cells(i, 113).Value
TBsubMenu38 = Worksheets("Data").Cells(i, 114).Value
TBsubMenu39 = Worksheets("Data").Cells(i, 115).Value
TBsubMenu30 = Worksheets("Data").Cells(i, 116).Value
TBsubMenu3Star = Worksheets("Data").Cells(i, 117).Value
TBsubMenu3Pound = Worksheets("Data").Cells(i, 118).Value

'Sub Menu 4 Options

TBsubMenu41 = Worksheets("Data").Cells(i, 119).Value
TBsubMenu42 = Worksheets("Data").Cells(i, 120).Value
TBsubMenu43 = Worksheets("Data").Cells(i, 121).Value
TBsubMenu44 = Worksheets("Data").Cells(i, 122).Value
TBsubMenu45 = Worksheets("Data").Cells(i, 123).Value
TBsubMenu46 = Worksheets("Data").Cells(i, 124).Value
TBsubMenu47 = Worksheets("Data").Cells(i, 125).Value
TBsubMenu48 = Worksheets("Data").Cells(i, 126).Value
TBsubMenu49 = Worksheets("Data").Cells(i, 127).Value
TBsubMenu40 = Worksheets("Data").Cells(i, 128).Value
TBsubMenu4Star = Worksheets("Data").Cells(i, 129).Value
TBsubMenu4Pound = Worksheets("Data").Cells(i, 130).Value

'Sub Menu 5 Options

TBsubMenu51 = Worksheets("Data").Cells(i, 131).Value
TBsubMenu52 = Worksheets("Data").Cells(i, 132).Value
TBsubMenu53 = Worksheets("Data").Cells(i, 133).Value
TBsubMenu54 = Worksheets("Data").Cells(i, 134).Value
TBsubMenu55 = Worksheets("Data").Cells(i, 135).Value
TBsubMenu56 = Worksheets("Data").Cells(i, 136).Value
TBsubMenu57 = Worksheets("Data").Cells(i, 137).Value
TBsubMenu58 = Worksheets("Data").Cells(i, 138).Value
TBsubMenu59 = Worksheets("Data").Cells(i, 139).Value
TBsubMenu50 = Worksheets("Data").Cells(i, 140).Value
TBsubMenu5Star = Worksheets("Data").Cells(i, 141).Value
TBsubMenu5Pound = Worksheets("Data").Cells(i, 142).Value

'Sub Menu 6 Options

TBsubMenu61 = Worksheets("Data").Cells(i, 143).Value
TBsubMenu62 = Worksheets("Data").Cells(i, 144).Value
TBsubMenu63 = Worksheets("Data").Cells(i, 145).Value
TBsubMenu64 = Worksheets("Data").Cells(i, 146).Value
TBsubMenu65 = Worksheets("Data").Cells(i, 147).Value
TBsubMenu66 = Worksheets("Data").Cells(i, 148).Value
TBsubMenu67 = Worksheets("Data").Cells(i, 149).Value
TBsubMenu68 = Worksheets("Data").Cells(i, 150).Value
TBsubMenu69 = Worksheets("Data").Cells(i, 151).Value
TBsubMenu60 = Worksheets("Data").Cells(i, 152).Value
TBsubMenu6Star = Worksheets("Data").Cells(i, 153).Value
TBsubMenu6Pound = Worksheets("Data").Cells(i, 154).Value

'____________Page 2 Call Tree Frame________________________

'Inserts a picture of the Call Tree on Page two

callTree.Picture = LoadPicture("Y:\Phillip\CCX Applications\Images\" & TBrecord.Text & ".jpg")

End If
Next
End Sub




'___________________________Add / Edit Record Button____________________________________________

Private Sub Add_Click()

    Dim cel As Range, rng As Range, v As Long, editRow As Long

Set rng = Worksheets("Data").Range("A:A")
On Error Resume Next
v = TBrecord.Value
If v > 0 Then editRow = rng.Find(v, lookat:=xlWhole).Row
If editRow = 0 Then
Set cel = rng.Find("*", searchdirection:=xlPrevious)
editRow = cel.Row + 1
TBrecord.Value = cel.Value + 1
End If
   
'____________Information Frame________________________

Worksheets("Data").Cells(editRow, 1).Value = TBrecord.Value
Worksheets("Data").Cells(editRow, 2).Value = TBccxName.Text
Worksheets("Data").Cells(editRow, 3).Value = TBlocation.Text
Worksheets("Data").Cells(editRow, 4).Value = TBcontact.Text
Worksheets("Data").Cells(editRow, 5).Value = TBemail.Text & "@childrensal.org"
Worksheets("Data").Cells(editRow, 6).Value = Format(TBtelephone.Text, "000-000-0000")


'____________Triggers Frame________________________

'Triggers

Worksheets("Data").Cells(editRow, 7).Value = TBtrigger1.Text
Worksheets("Data").Cells(editRow, 8).Value = TBtrigger2.Text
Worksheets("Data").Cells(editRow, 9).Value = TBtrigger3.Text
Worksheets("Data").Cells(editRow, 10).Value = TBtrigger4.Text
Worksheets("Data").Cells(editRow, 11).Value = TBtrigger5.Text
Worksheets("Data").Cells(editRow, 12).Value = TBtrigger6.Text
Worksheets("Data").Cells(editRow, 13).Value = TBtrigger7.Text
Worksheets("Data").Cells(editRow, 14).Value = TBtrigger8.Text

'Sesssion Limits

Worksheets("Data").Cells(editRow, 15).Value = CBsession1.Text
Worksheets("Data").Cells(editRow, 16).Value = CBsession2.Text
Worksheets("Data").Cells(editRow, 17).Value = CBsession3.Text
Worksheets("Data").Cells(editRow, 18).Value = CBsession4.Text
Worksheets("Data").Cells(editRow, 19).Value = CBsession5.Text
Worksheets("Data").Cells(editRow, 20).Value = CBsession6.Text
Worksheets("Data").Cells(editRow, 21).Value = CBsession7.Text
Worksheets("Data").Cells(editRow, 22).Value = CBsession8.Text

'Call Control Groups

Worksheets("Data").Cells(editRow, 23).Value = CBcallControl1.Text
Worksheets("Data").Cells(editRow, 24).Value = CBcallControl2.Text
Worksheets("Data").Cells(editRow, 25).Value = CBcallControl3.Text
Worksheets("Data").Cells(editRow, 26).Value = CBcallControl4.Text
Worksheets("Data").Cells(editRow, 27).Value = CBcallControl5.Text
Worksheets("Data").Cells(editRow, 28).Value = CBcallControl6.Text
Worksheets("Data").Cells(editRow, 29).Value = CBcallControl7.Text
Worksheets("Data").Cells(editRow, 30).Value = CBcallControl8.Text
'____________Scripts Frame________________________
'Scripts

If TBscripts1.Value <> "" And Left(TBscripts1.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 31).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts2.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 32).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 32).Value = TBscripts1.Text
End If


If TBscripts1.Value <> "" And Left(TBscripts3.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 33).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 33).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts4.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 34).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 34).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts5.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 35).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 35).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts6.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 36).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 36).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts7.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 37).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 37).Value = TBscripts1.Text
End If

If TBscripts1.Value <> "" And Left(TBscripts8.Value, 1) <> "[" Then
Worksheets("Data").Cells(editRow, 38).Value = "[" & TBscripts1.Text & "IVR.aef]"
Else
Worksheets("Data").Cells(editRow, 38).Value = TBscripts1.Text
End If

'____________Prompts Frame________________________
 
'Prompt Types

 Worksheets("Data").Cells(editRow, 39).Value = CBprompt1.Text
Worksheets("Data").Cells(editRow, 40).Value = CBprompt2.Text
Worksheets("Data").Cells(editRow, 41).Value = CBprompt3.Text
Worksheets("Data").Cells(editRow, 42).Value = CBprompt4.Text
Worksheets("Data").Cells(editRow, 43).Value = CBprompt5.Text
Worksheets("Data").Cells(editRow, 44).Value = CBprompt6.Text
Worksheets("Data").Cells(editRow, 45).Value = CBprompt7.Text
Worksheets("Data").Cells(editRow, 46).Value = CBprompt8.Text

'Open Time

Worksheets("Data").Cells(editRow, 47).Value = VBA.Format$(CBopen1.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 48).Value = VBA.Format$(CBopen2.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 49).Value = VBA.Format$(CBopen3.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 50).Value = VBA.Format$(CBopen4.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 51).Value = VBA.Format$(CBopen5.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 52).Value = VBA.Format$(CBopen6.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 53).Value = VBA.Format$(CBopen7.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 54).Value = VBA.Format$(CBopen8.Value, "hh:Nn")

'Close Time

Worksheets("Data").Cells(editRow, 55).Value = VBA.Format$(CBclose1.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 56).Value = VBA.Format$(CBclose2.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 57).Value = VBA.Format$(CBclose3.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 58).Value = VBA.Format$(CBclose4.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 59).Value = VBA.Format$(CBclose5.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 60).Value = VBA.Format$(CBclose6.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 61).Value = VBA.Format$(CBclose7.Value, "hh:Nn")
Worksheets("Data").Cells(editRow, 62).Value = VBA.Format$(CBclose8.Value, "hh:Nn")

'Wave Files


If TBscripts1.Value <> "" Then Worksheets("Data").Cells(editRow, 63).Value = TBwave1.Text & ".wav"
If TBscripts2.Value <> "" Then Worksheets("Data").Cells(editRow, 64).Value = TBwave2.Text & ".wav"
If TBscripts3.Value <> "" Then Worksheets("Data").Cells(editRow, 65).Value = TBwave3.Text & ".wav"
If TBscripts4.Value <> "" Then Worksheets("Data").Cells(editRow, 66).Value = TBwave4.Text & ".wav"
If TBscripts5.Value <> "" Then Worksheets("Data").Cells(editRow, 67).Value = TBwave5.Text & ".wav"
If TBscripts6.Value <> "" Then Worksheets("Data").Cells(editRow, 68).Value = TBwave6.Text & ".wav"
If TBscripts7.Value <> "" Then Worksheets("Data").Cells(editRow, 69).Value = TBwave7.Text & ".wav"
If TBscripts8.Value <> "" Then Worksheets("Data").Cells(editRow, 70).Value = TBwave8.Text & ".wav"

'____________Main Menu Frame________________________

'Main Menu 1 Options

Worksheets("Data").Cells(editRow, 71).Value = TBmainMenu1.Text
Worksheets("Data").Cells(editRow, 72).Value = TBmainMenu2.Text
Worksheets("Data").Cells(editRow, 73).Value = TBmainMenu3.Text
Worksheets("Data").Cells(editRow, 74).Value = TBmainMenu4.Text
Worksheets("Data").Cells(editRow, 75).Value = TBmainMenu5.Text
Worksheets("Data").Cells(editRow, 76).Value = TBmainMenu6.Text
Worksheets("Data").Cells(editRow, 77).Value = TBmainMenu7.Text
Worksheets("Data").Cells(editRow, 78).Value = TBmainMenu8.Text
Worksheets("Data").Cells(editRow, 79).Value = TBmainMenu9.Text
Worksheets("Data").Cells(editRow, 80).Value = TBmainMenu0.Text
Worksheets("Data").Cells(editRow, 81).Value = TBmainMenuStar.Text
Worksheets("Data").Cells(editRow, 82).Value = TBmainMenuPound.Text

'____________Sub Menu Frame________________________

'Sub Menu 1 Options

Worksheets("Data").Cells(editRow, 83).Value = TBsubMenu11.Text
Worksheets("Data").Cells(editRow, 84).Value = TBsubMenu12.Text
Worksheets("Data").Cells(editRow, 85).Value = TBsubMenu13.Text
Worksheets("Data").Cells(editRow, 86).Value = TBsubMenu14.Text
Worksheets("Data").Cells(editRow, 87).Value = TBsubMenu15.Text
Worksheets("Data").Cells(editRow, 88).Value = TBsubMenu16.Text
Worksheets("Data").Cells(editRow, 89).Value = TBsubMenu17.Text
Worksheets("Data").Cells(editRow, 90).Value = TBsubMenu18.Text
Worksheets("Data").Cells(editRow, 91).Value = TBsubMenu19.Text
Worksheets("Data").Cells(editRow, 92).Value = TBsubMenu10.Text
Worksheets("Data").Cells(editRow, 93).Value = TBsubMenu1Star.Text
Worksheets("Data").Cells(editRow, 94).Value = TBsubMenu1Pound.Text

'Sub Menu 2 Options

Worksheets("Data").Cells(editRow, 95).Value = TBsubMenu21.Text
Worksheets("Data").Cells(editRow, 96).Value = TBsubMenu22.Text
Worksheets("Data").Cells(editRow, 97).Value = TBsubMenu23.Text
Worksheets("Data").Cells(editRow, 98).Value = TBsubMenu24.Text
Worksheets("Data").Cells(editRow, 99).Value = TBsubMenu25.Text
Worksheets("Data").Cells(editRow, 100).Value = TBsubMenu26.Text
Worksheets("Data").Cells(editRow, 101).Value = TBsubMenu27.Text
Worksheets("Data").Cells(editRow, 102).Value = TBsubMenu28.Text
Worksheets("Data").Cells(editRow, 103).Value = TBsubMenu29.Text
Worksheets("Data").Cells(editRow, 104).Value = TBsubMenu20.Text
Worksheets("Data").Cells(editRow, 105).Value = TBsubMenu2Star.Text
Worksheets("Data").Cells(editRow, 106).Value = TBsubMenu2Pound.Text


'Sub Menu 3 Options

Worksheets("Data").Cells(editRow, 107).Value = TBsubMenu31.Text
Worksheets("Data").Cells(editRow, 108).Value = TBsubMenu32.Text
Worksheets("Data").Cells(editRow, 109).Value = TBsubMenu33.Text
Worksheets("Data").Cells(editRow, 110).Value = TBsubMenu34.Text
Worksheets("Data").Cells(editRow, 111).Value = TBsubMenu35.Text
Worksheets("Data").Cells(editRow, 112).Value = TBsubMenu36.Text
Worksheets("Data").Cells(editRow, 113).Value = TBsubMenu37.Text
Worksheets("Data").Cells(editRow, 114).Value = TBsubMenu38.Text
Worksheets("Data").Cells(editRow, 115).Value = TBsubMenu39.Text
Worksheets("Data").Cells(editRow, 116).Value = TBsubMenu30.Text
Worksheets("Data").Cells(editRow, 117).Value = TBsubMenu3Star.Text
Worksheets("Data").Cells(editRow, 118).Value = TBsubMenu3Pound.Text



'Sub Menu 4 Options

Worksheets("Data").Cells(editRow, 119).Value = TBsubMenu41.Text
Worksheets("Data").Cells(editRow, 120).Value = TBsubMenu42.Text
Worksheets("Data").Cells(editRow, 121).Value = TBsubMenu43.Text
Worksheets("Data").Cells(editRow, 122).Value = TBsubMenu44.Text
Worksheets("Data").Cells(editRow, 123).Value = TBsubMenu45.Text
Worksheets("Data").Cells(editRow, 124).Value = TBsubMenu46.Text
Worksheets("Data").Cells(editRow, 125).Value = TBsubMenu47.Text
Worksheets("Data").Cells(editRow, 126).Value = TBsubMenu48.Text
Worksheets("Data").Cells(editRow, 127).Value = TBsubMenu49.Text
Worksheets("Data").Cells(editRow, 128).Value = TBsubMenu40.Text
Worksheets("Data").Cells(editRow, 129).Value = TBsubMenu4Star.Text
Worksheets("Data").Cells(editRow, 130).Value = TBsubMenu4Pound.Text

'Sub Menu 5 Options

Worksheets("Data").Cells(editRow, 131).Value = TBsubMenu51.Text
Worksheets("Data").Cells(editRow, 132).Value = TBsubMenu52.Text
Worksheets("Data").Cells(editRow, 133).Value = TBsubMenu53.Text
Worksheets("Data").Cells(editRow, 134).Value = TBsubMenu54.Text
Worksheets("Data").Cells(editRow, 135).Value = TBsubMenu55.Text
Worksheets("Data").Cells(editRow, 136).Value = TBsubMenu56.Text
Worksheets("Data").Cells(editRow, 137).Value = TBsubMenu57.Text
Worksheets("Data").Cells(editRow, 138).Value = TBsubMenu58.Text
Worksheets("Data").Cells(editRow, 139).Value = TBsubMenu59.Text
Worksheets("Data").Cells(editRow, 140).Value = TBsubMenu50.Text
Worksheets("Data").Cells(editRow, 141).Value = TBsubMenu5Star.Text
Worksheets("Data").Cells(editRow, 142).Value = TBsubMenu5Pound.Text


'Sub Menu 6 Options

Worksheets("Data").Cells(editRow, 143).Value = TBsubMenu61.Text
Worksheets("Data").Cells(editRow, 144).Value = TBsubMenu62.Text
Worksheets("Data").Cells(editRow, 145).Value = TBsubMenu63.Text
Worksheets("Data").Cells(editRow, 146).Value = TBsubMenu64.Text
Worksheets("Data").Cells(editRow, 147).Value = TBsubMenu65.Text
Worksheets("Data").Cells(editRow, 148).Value = TBsubMenu66.Text
Worksheets("Data").Cells(editRow, 149).Value = TBsubMenu67.Text
Worksheets("Data").Cells(editRow, 150).Value = TBsubMenu68.Text
Worksheets("Data").Cells(editRow, 151).Value = TBsubMenu69.Text
Worksheets("Data").Cells(editRow, 152).Value = TBsubMenu60.Text
Worksheets("Data").Cells(editRow, 153).Value = TBsubMenu6Star.Text
Worksheets("Data").Cells(editRow, 154).Value = TBsubMenu6Pound.Text

' _____________Message Box Record Added________________

MsgBox "Your Record has been Processed"

End Sub

'____________Initialization__________________________
'____________Combo Boxes________________________

Private Sub UserForm_Initialize()
Search_Click
Dim i As Long
Dim Ary As Variant

'ComboBoxSession 1-10 Page 1

Ary = Array("1 port", "2 ports", "3 ports", "4 ports", "5 ports", "6 ports", "7 ports", "8 ports", "9 ports", "10 ports")
For i = 1 To 8
Me.Controls("CBsession" & i).List = Ary
Next i

'ComboBoxCallControl 1-10 Page 1

Ary = Array("UMC Telephony Group 1", "UMC Telephony Group 2", "UMC Telephony Group 3", "UMC Telephony Group 4", "UMC Telephony Group 5", "UMC Telephony Group 6", "UMC Telephony Group 7", "UMC Telephony Group 8", "UMC Telephony Group 9", "UMC Telephony Group 10")
For i = 1 To 8
Me.Controls("CBcallControl" & i).List = Ary
Next i

'CBpromptTypes 1-10 Page 2

Ary = Array("Welcome", "Closed", "Holiday", "Weather")
For i = 1 To 8
Me.Controls("CBprompt" & i).List = Ary
Next i

'CBpromptOpen 1-10 Page 2

Ary = Array("7:00 AM", "7:30 AM", "8:00 AM", "8:30 AM", "9:00 AM", "9:30 AM", "10:00 AM", "10:30 AM", "11:00 AM", "11:30 AM", "12:00 PM", "12:30 PM", "1:00 PM", "1:30 PM", "2:00 PM", "2:30 PM", "3:00 PM", "4:00 PM", "4:30 PM", "5:00 PM", "5:30 PM", "6:00 PM")
For i = 1 To 8
Me.Controls("CBopen" & i).List = Ary
Next I

'CBpromptClose 1-10 Page 2

Ary = Array("7:00 AM", "7:30 AM", "8:00 AM", "8:30 AM", "9:00 AM", "9:30 AM", "10:00 AM", "10:30 AM", "11:00 AM", "11:30 AM", "12:00 PM", "12:30 PM", "1:00 PM", "1:30 PM", "2:00 PM", "2:30 PM", "3:00 PM", "4:00 PM", "4:30 PM", "5:00 PM", "5:30 PM", "6:00 PM")
For i = 1 To 8
Me.Controls("CBclose" & i).List = Ary
Next i


   End Sub[/SIZE]
 
Upvote 0
Ok, thanks for that. The problem is with this line
VBA Code: 
Record = Val(TBrecord)
When that is run from the Initialize event the textbox will not have a value, one way round that is to use
VBA Code: 
If TBrecord.Value = "" Then
   Record = "1"
Else
   Record = Val(TBrecord)
End If
 
Upvote 0
Thanks for looking at the problem. I added your code to the Search Routine in the userform, and I still have the same issue.

Please look at my code for Button 1. Notice that the Call sub line is in red. Isn’t this the reason that I’m getting a Syntax error?
 

Attachments

  • Sub for Button 1 in Module 1.JPG
    Sub for Button 1 in Module 1.JPG
    20.4 KB · Views: 2
  • Sub for Search in CCX_Form.JPG
    Sub for Search in CCX_Form.JPG
    28.2 KB · Views: 2
Upvote 0
You need to remove it from the button & call it from the initialize
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
VBA Error on userform (Run-time error '91')
Replies
5
Views
89
cerberus1845
C
A
  • Question
Help Using VBA To Create A Workbook Containing VBA
Replies
2
Views
400
Aldk_1
A
G
  • Question
Bug with passing variables between user forms--VBA
Replies
4
Views
87
Domenic
D
K
  • Question
VBA UserForm.Lable.Caption VALUE FOR INSERT CELL
Replies
4
Views
219
dmt32
D
I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I

Forum statistics

Threads
1,214,919
Messages
6,122,260
Members
449,075
Latest member
staticfluids

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top