Sprock
New Member
- Joined
- Jun 16, 2011
- Messages
- 7
My VBA Form works fine in windows but when offsite users open the file via Citrix, it bugs out at the
line in the code below with "Methods "Worksheets" of Object '_Global' failed "
.
Any whay to fix this ??
Code:
Set ws = Worksheets("Data")
Code:
Private Sub Label1_Click()
End Sub
Private Sub Label6_Click()
End Sub
Private Sub lbl_terminal_Click()
End Sub
Private Sub UserForm_Activate()
Me.txtDate.Value = Date
Dim tDate As Date
tDate = (Int(Date))
txtDate.Text = Format(tDate, "yyyymmmdd")
End Sub
Private Sub img_logo_Click()
End Sub
Private Sub Label7_Click()
End Sub
Private Sub TerminalName_Change()
End Sub
Private Sub txtDate_Change()
End Sub
Private Sub txtInitials_Change()
End Sub
Private Sub txtInitials_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("A") To Asc("z")
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub txtNumber_Change()
End Sub
Private Sub txtNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Asc("0") To Asc("9")
Case Else
KeyAscii = 0
End Select
End Sub
Private Sub txtFile_Change()
End Sub
Private Sub txtFile_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
' If right-button clicked
If Button = 2 Then
Call ShowPopup(Me, Me.Caption, X, Y)
End If
End Sub
Private Sub BuildFileName_Click()
Me.[txtFile] = [TerminalName] & "_" & [txtInitials] & [txtNumber] & "_" & [txtDate] & ".pdf"
'check for a terminal name
If (Me.TerminalName.Value) = "" Then
Me.TerminalName.SetFocus
MsgBox "Please select a Terminal Name"
Exit Sub
End If
'check for a Unit ititials
If (Me.txtInitials.Value) = "" Then
Me.txtInitials.SetFocus
MsgBox "Please enter the Unit's Initials"
Exit Sub
End If
If Len(Me.txtInitials) < 3 Then
MsgBox "A minimum of 3 characters is needed for the Unit's Initials"
End If
'check for a Unit Number
If (Me.txtNumber.Value) = "" Then
Me.txtNumber.SetFocus
MsgBox "Please enter the Unit's Number"
Exit Sub
End If
If Len(Me.txtNumber) < 5 Then
MsgBox "A minimum of 5 characters is needed for the Unit's ID #"
End If
With Me.txtFile
.SelStart = 0
.SelLength = Len(.Text)
End With
End Sub
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Data")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a terminal name
If (Me.TerminalName.Value) = "" Then
Me.TerminalName.SetFocus
MsgBox "Please select a Terminal Name"
Exit Sub
End If
'check for a Unit ititials
If (Me.txtInitials.Value) = "" Then
Me.txtInitials.SetFocus
MsgBox "Please enter the Unit's Initials"
Exit Sub
End If
'check for a Unit Number
If (Me.txtNumber.Value) = "" Then
Me.txtNumber.SetFocus
MsgBox "Please enter the Unit's Number"
Exit Sub
End If
'check for a File Name
If (Me.txtFile.Value) = "" Then
Me.txtFile.SetFocus
MsgBox "You must create a unique file name for the PDF by clicking the Build File Name button"
Exit Sub
End If
'check for a Comment
If (Me.txtComment.Value) = "" Then
Me.txtComment.SetFocus
MsgBox "You must add a comment"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TerminalName.Value
ws.Cells(iRow, 2).Value = Me.txtInitials.Value
ws.Cells(iRow, 3).Value = Me.txtNumber.Value
ws.Cells(iRow, 4).Formula = "=HYPERLINK(""" & "X:\Intermodal\CSXITPP_Data\Event_Log_PDFs\" & Me.txtFile.Value & """)"
ws.Cells(iRow, 5).Value = Me.txtComment.Value
ws.Cells(iRow, 6).Value = Now
'clear the data
Me.TerminalName.Value = ""
Me.txtInitials.Value = ""
Me.txtNumber.Value = ""
Me.txtFile.Value = ""
Me.txtComment.Value = ""
Me.TerminalName.SetFocus
ThisWorkbook.Save
End Sub
Private Sub UploadPDF_Click()
ThisWorkbook.FollowHyperlink "X:\Intermodal\CSXITPP_Data\Event_Log_PDFs"
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub
Private Sub cmdClose_Click()
Me.txtDate.Value = ""
Unload Me
End SubAny whay to fix this ??
