ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Amend form code to allow an option (https://www.excelbanter.com/excel-programming/280412-amend-form-code-allow-option.html)

Stuart[_5_]
Amend form code to allow an option
 
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else
frmName_Contractors.lbDataCode.List = _
Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

If user choses Yes, then all is fine. The sub 'DataStoreCodes'
loads col1 in the listbox of frmName_Contractors.Show
with all the codes in the user's workbook, and then checks them
against a sheet (MasterDataStore) in an addin. If the code is in the
addin, then it's loaded into Col2 in the listbox against the relevent
item.

If user chooses No, then I cannot get it to work.
The range is correctly added into Col! of the listbox, and when the
form displays, Col2 is (correctly) empty of values.
At this point user should be able to click on a Col1 entry then go
to a textbox where they type the corresponding Col2 value.

Doing this gives an error:
"Could not get the Column property....Invalid argument"

Here is the form code:

Private Sub lbDataCode_Click()
If lbDataCode.ListIndex < -1 Then
If lbDataCode.Column(1, lbDataCode.ListIndex) < "" Then
tbCtrTrade.Text = lbDataCode.Column(1, lbDataCode.ListIndex)
tbCtrTrade.SetFocus
tbCtrTrade.SelStart = 0
tbCtrTrade.SelLength = Len(tbCtrTrade.Text)
Else
tbCtrTrade.Text = ""
tbCtrTrade.SetFocus
End If
Else
tbCtrTrade.Text = ""
End If
End Sub

Private Sub tbCtrTrade_AfterUpdate()
If lbDataCode.ListIndex < -1 Then
If tbCtrTrade.Text < "" Then
lbDataCode.Column(1, lbDataCode.ListIndex) _
= tbCtrTrade.Text
End If
End If
End Sub

How may I amend this code, please?.

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

Tom Ogilvy
Amend form code to allow an option
 
Dim varr as Variant
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else

varr = Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
Redim Preserve varr(1 to Ubound(varr), 1 to 2)
frmName_Contractors.lbDataCode.List = Varr

With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else
frmName_Contractors.lbDataCode.List = _
Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

If user choses Yes, then all is fine. The sub 'DataStoreCodes'
loads col1 in the listbox of frmName_Contractors.Show
with all the codes in the user's workbook, and then checks them
against a sheet (MasterDataStore) in an addin. If the code is in the
addin, then it's loaded into Col2 in the listbox against the relevent
item.

If user chooses No, then I cannot get it to work.
The range is correctly added into Col! of the listbox, and when the
form displays, Col2 is (correctly) empty of values.
At this point user should be able to click on a Col1 entry then go
to a textbox where they type the corresponding Col2 value.

Doing this gives an error:
"Could not get the Column property....Invalid argument"

Here is the form code:

Private Sub lbDataCode_Click()
If lbDataCode.ListIndex < -1 Then
If lbDataCode.Column(1, lbDataCode.ListIndex) < "" Then
tbCtrTrade.Text = lbDataCode.Column(1, lbDataCode.ListIndex)
tbCtrTrade.SetFocus
tbCtrTrade.SelStart = 0
tbCtrTrade.SelLength = Len(tbCtrTrade.Text)
Else
tbCtrTrade.Text = ""
tbCtrTrade.SetFocus
End If
Else
tbCtrTrade.Text = ""
End If
End Sub

Private Sub tbCtrTrade_AfterUpdate()
If lbDataCode.ListIndex < -1 Then
If tbCtrTrade.Text < "" Then
lbDataCode.Column(1, lbDataCode.ListIndex) _
= tbCtrTrade.Text
End If
End If
End Sub

How may I amend this code, please?.

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

Stuart[_5_]
Amend form code to allow an option
 
Thanks very much.

If there's time, one other general question, please:

How to prevent the user exiting the form until
all Col1 entries have a corresponding Col2 value?

Regards and thanks.

"Tom Ogilvy" wrote in message
...
Dim varr as Variant
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else

varr = Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
Redim Preserve varr(1 to Ubound(varr), 1 to 2)
frmName_Contractors.lbDataCode.List = Varr

With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else
frmName_Contractors.lbDataCode.List = _
Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

If user choses Yes, then all is fine. The sub 'DataStoreCodes'
loads col1 in the listbox of frmName_Contractors.Show
with all the codes in the user's workbook, and then checks them
against a sheet (MasterDataStore) in an addin. If the code is in the
addin, then it's loaded into Col2 in the listbox against the relevent
item.

If user chooses No, then I cannot get it to work.
The range is correctly added into Col! of the listbox, and when the
form displays, Col2 is (correctly) empty of values.
At this point user should be able to click on a Col1 entry then go
to a textbox where they type the corresponding Col2 value.

Doing this gives an error:
"Could not get the Column property....Invalid argument"

Here is the form code:

Private Sub lbDataCode_Click()
If lbDataCode.ListIndex < -1 Then
If lbDataCode.Column(1, lbDataCode.ListIndex) < "" Then
tbCtrTrade.Text = lbDataCode.Column(1, lbDataCode.ListIndex)
tbCtrTrade.SetFocus
tbCtrTrade.SelStart = 0
tbCtrTrade.SelLength = Len(tbCtrTrade.Text)
Else
tbCtrTrade.Text = ""
tbCtrTrade.SetFocus
End If
Else
tbCtrTrade.Text = ""
End If
End Sub

Private Sub tbCtrTrade_AfterUpdate()
If lbDataCode.ListIndex < -1 Then
If tbCtrTrade.Text < "" Then
lbDataCode.Column(1, lbDataCode.ListIndex) _
= tbCtrTrade.Text
End If
End If
End Sub

How may I amend this code, please?.

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003

Tom Ogilvy
Amend form code to allow an option
 
loop through the entries and look. Then decide whether to exit or not.

You could do this in the click event of the control you have provided to
exit and also in the queryclose event.

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
Thanks very much.

If there's time, one other general question, please:

How to prevent the user exiting the form until
all Col1 entries have a corresponding Col2 value?

Regards and thanks.

"Tom Ogilvy" wrote in message
...
Dim varr as Variant
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else

varr = Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
Redim Preserve varr(1 to Ubound(varr), 1 to 2)
frmName_Contractors.lbDataCode.List = Varr

With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
If MsgBox("Do you wish to use the Master SubContractor/" _
& "Suppliers" & vbNewLine & "trade reference codes, or" _
& "rename them again?", vbYesNo) = vbYes Then
Call DataStoreCodes(wkbkname)
Else
frmName_Contractors.lbDataCode.List = _
Workbooks(wkbkname).Worksheets _
("DataStore").Range("G1", "G" & Rows.Count).Value
With Workbooks(wkbkname).Sheets("DataStore")
.Unprotect
.Range("G1", "G" & .Rows.Count).ClearContents
End With
End If

frmName_Contractors.Show

If user choses Yes, then all is fine. The sub 'DataStoreCodes'
loads col1 in the listbox of frmName_Contractors.Show
with all the codes in the user's workbook, and then checks them
against a sheet (MasterDataStore) in an addin. If the code is in the
addin, then it's loaded into Col2 in the listbox against the relevent
item.

If user chooses No, then I cannot get it to work.
The range is correctly added into Col! of the listbox, and when the
form displays, Col2 is (correctly) empty of values.
At this point user should be able to click on a Col1 entry then go
to a textbox where they type the corresponding Col2 value.

Doing this gives an error:
"Could not get the Column property....Invalid argument"

Here is the form code:

Private Sub lbDataCode_Click()
If lbDataCode.ListIndex < -1 Then
If lbDataCode.Column(1, lbDataCode.ListIndex) < "" Then
tbCtrTrade.Text = lbDataCode.Column(1, lbDataCode.ListIndex)
tbCtrTrade.SetFocus
tbCtrTrade.SelStart = 0
tbCtrTrade.SelLength = Len(tbCtrTrade.Text)
Else
tbCtrTrade.Text = ""
tbCtrTrade.SetFocus
End If
Else
tbCtrTrade.Text = ""
End If
End Sub

Private Sub tbCtrTrade_AfterUpdate()
If lbDataCode.ListIndex < -1 Then
If tbCtrTrade.Text < "" Then
lbDataCode.Column(1, lbDataCode.ListIndex) _
= tbCtrTrade.Text
End If
End If
End Sub

How may I amend this code, please?.

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.528 / Virus Database: 324 - Release Date: 16/10/2003


All times are GMT +1. The time now is 02:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com