Debugging Userform

[sswcharlie]

Hi everyone

I have done a user form awith a combobox and sever txt box and cmd buttons. Started with a code off net and changed to suit.

However I cannot get it to run . Have done the F8 etc, but as I am new to userforms I may have something minor wrong (or major!)

Can I send WB to someone to check for me ? or maybe upload to the group. A bit confidential and I would want it to come down asap for group. For starters this is the code and I am trying to run from the 'run' in vba.

[VBA]

Private Sub TextBox2_Change()
End Sub
Private Sub SelectCo_Change()
End Sub
Private Sub CombSelectCo_Change()
End Sub
Private Sub TxtProduct_Change()
End Sub
Private Sub UserForm_Click()
End Sub
frmSelectCo
Dim NotNow As Boolean

Private Sub cmdCancel_Click()
'Unload the userform
Unload Me
End Sub
Private Sub cmdEnter_Click()
NotNow = True

N = Application.Match(Me.cmbCombSelectCo.Value, Range("g12:m"), 0)


Cells(N, 1).Value = Me.TxtCoName.Text
Cells(N, 2).Value = Me.TxtProduct.Text
Cells(N, 3).Value = Me.TxtCountry.Text
'more codes to go in here
NotNow = False
End Sub
Private Sub cmbCombSelectCo_Change()
If NotNow Then Exit Sub
'vRange = ":g12:m" & Cells(Rows.Count, "g").End(xlUp).Row
vrange = "Contacts"
TxtCoName.Text = Application.VLookup(cmbItemName.Value, Sheets("Contacts").Range(vrange), 1, False)
TxtProduct.Text = Application.VLookup(cmbItemName.Value, Sheets("Contacts").Range(vrange), 2, False)
TxtCountry.Text = Application.VLookup(cmbItemName.Value, Sheets("Contacts").Range(vrange), 3, False)
'more codes to go in here
End Sub



Private Sub UserForm_Initialize()
'UserForm2.SelectCo.RowSource = "g12:m" & Cells(Rows.Count, "g").End(xlUp).Row
frmSelectCo.cmbSelectCo.RowSource = "Contacts"
End Sub

[/VBA]

The userforms picks up the CompanyName data from a dynamic range called 'Contacts' which is in Contacts (sheet) starts at column g10 thru to column m
Vlookup puts the next three row details into the 3 boxes on user form, edit can then be done on data and using enter to update the Contacts dynamic range data.
All the txt boxes and cmb, and cmd buttons are all set up.

The Initialise code had the ' in front when I downloaded from athe net. Not sure if I should remove etc.

Some guidance would be appreciated.

Thanks

Charlie Harris or
Downunder

[pjotter]

You should call the Userform somewhere in another module, or by assigning it to a commandbutton on a sheet or on another event.

You can call the userform with the following code:

Userform1.show (if this is userform1)
So just make a module and paste this code in it:

[VBA]
Sub ExecuteUserform()
Userform1.show
end sub
[/VBA]

Then run ExecuteUserform from the macro list in Excel(Alt+F8) or execute run in vba.

Atleast I'm guessing that's what you mean to do and that is your problem.
If not, please be more specific and clear.

If this solution is correct, you can debug code with the debugger, cause it looks a bit bugged.

[Rob342]

Hi Charlie,

I am currently working on a project with user forms.

You need to look at Private Sub UserForm_Initialize()
What i can see that its not doing that

You can also open the userform in the Workbook open event with
"NameOfYourForm".Show

Really need to see a copy WB to debug
Can you post a copy with all sensative data removed ?
Rob

[sswcharlie]

Hi Guys

Thanks for your response's. Have done as per your notes.

Have put the .show in but opens up a different userform and also blank!

Have attached workbook here. See some notes on TABS sheet (should open at that sheet) There is a lot of unfinished work and duplicates in the workbook. Please ignore these.

With this exercise I want to have a userform to open the 'Contacts' sheet and enable user to edit the boxes and then put new info back into the 'contacts' sheet. The contact sheet is only partly populated but there should be enough info to make it work.

I will learn from this. I will follow thru with the advice I receive.

Thanks and regards


Moderator: Can you delte the workbook from VBAX after 2 or 3 downloads. Thanks.

[Rob342]

Hi charlie

Question, which user form is supposed to open with the button?
Also this is a form control button not active x there's no macro assigned to the form button, do you want it replacing with a command button.
There's a lot of data there, i'll take a quick look tomorrow ok.

Might need to get back to you for more info ok, watch out for new messages

When i get the form working i can send the WB to you via PM message ok

Rob

[sswcharlie]

Hi Raob

Thanks for the message.
The userform for this exercise in Project Explorer-Forms, is called SelectCo although when the userform edit pops up it is called 'userform2'

Yes replace with the correct command button.

Thanks and regards
Charlie

[Rob342]

Hi Charlie

The code for the update to amend company details, its already in the WB ok
[vba]
Private Sub CompUpdate()
'// If details change on company users only, then update with new details
Dim ws As Worksheet
Dim TxtProduct As String
Dim TxtCountry As String
Dim TxtContact As String
Dim TxtEmail As String
Dim TxtPhone As String
Dim TxtFax As String
Dim MyComp As String, c As Range
MyComp = Me.cboSelectCo
Set c = Worksheets("ContactDB").Columns("A:A").Find(MyComp, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
'// Charlie have rem out the 3 last fields untill you have added the boxes on the form to update them ok

c.Offset(0, 1).Value = Me.TxtProduct.Value 'Update the product field
c.Offset(0, 2).Value = Me.TxtCountry.Value 'Update the Country field
c.Offset(0, 3).Value = Me.TxtContact.Value 'Update the contact field
'c.Offset(0, 4).Value = Me.TxtEmail.Value 'Update The email field
'c.Offset(0, 5).Value = Me.TxtPhone.Value 'Update the phone number field
'c.Offset(0, 6).Value = Me.TxtFax.Value 'Update the fax number field

End If
End Sub
[/vba]

Regards
Rob

Ps if you need anymore help either create another post or email me ok