Posts by CJamesUK

Thanks Roy tbh ive not got a clue on how to extract what i need out of that sheet

Quote from royUK

Here's an example that I created for another question. Have a look and let me know if you need help adapting it.

Ok thats alot better than what i was looking at doing

Quote from royUK

I've looked at your userform ProductEdit and it contains one ListBox and one CommandButton. You cannot edit within a ListBox, you need to select an item and populate TextBoxes with the relevant information which can be edited.

I'll find an example for you

Yes it does that aleardy another box opens up you amend the text see productformedit2

Forgot to add on front userform box bottom left admin gets you in password cj

Quote from royUK

Attach an example workbook

Hi Roy please see attached, your looking at the userform productEdit its all a bit of a mess was just trying to get it working them was going to use the same process with other areas.

Also while we are at it, i want to add a stock sheet and i dont really know the best way to go about it so any advice would be grateful but somewhere either in VBA of just the sheets i want it to automatically deduct stock as i make my pick list / order sheet.

Morning All,

So ive got a listbox which takes data from a sheet which then i can edit but i now want to send that edited data back to the same sheet and overwrite.

i know how to do it adding the data back to the sheet in the next clear cell (coded added)

can anyone help?

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ProductFormEdit2.TextBox1.Text = Me.ListBox1.Column(0)
'ProdcutFormEdit2.TextBox2.Text = Me.ListBox1.Column(1)
ProductFormEdit2.Show
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
For i = 1 To Application.WorksheetFunction.CountA(Sheet4.Range("A:A"))
Me.ListBox1.AddItem Sheet4.Cells(i, 1).Value
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet4.Cells(i, 2).Value
Next i
End Sub
Private Sub CommandButton1_Click()
Dim r As Long, lrf As Long

For r = 0 To ListBox1.ListCount - 1
With Sheets("Suppliers")
lrf = Sheets("Suppliers").Range("A" & Rows.Count).End(xlUp).Row
Sheets("Suppliers").Range("A65000").End(xlUp).Offset(1, 0).Value = ListBox1.List(r, 0)
Sheets("Suppliers").Range("A65000").End(xlUp).Offset(0, 1).Value = ListBox1.List(r, 1)

End With

Next r
End Sub

Quote from Mumps

Try:

Code

Private Sub CommandButton7_Click()
    Application.Visible = True
    Unload UserForm1
    Sheets("PurchaseOrder").Range("PurchaseOrder").PrintPreview
    Call AutoEmail
    Application.Visible = False
    UserForm1.Show
End Sub

Cheers Mumps works lovely ill get the hang of this soon

Quote from Mumps

In a previous post you said that you already have an email macro. Can you post that code?

It in the sheet posted but ive been using the code in a module and using a button on the tabs see module 5

Dont suppose i can use the same code to preview and send the email?

Quote from Mumps

Try:

Code

Private Sub CommandButton7_Click()
    Application.Visible = True
    Unload UserForm1
    Sheets("PurchaseOrder").Range("PurchaseOrder").PrintPreview
    Application.Visible = False
    UserForm1.Show
End Sub

That works perfect thank you!!

So im trying to get a cmdbutton to print preveiw a certain sheet, when i can get one working ill also need it to create an email to which there is already a macro for

Quote from royUK

You need to hide the userform first, that will prevent the "hanging"

Code

Private Sub CommandButton1_Click()
Me.Hide
ActiveSheet.PrintPreview
Me.Show
End Sub

Thanks Roy

That didnt work unfortunalty, im going to post the sheet up

theres quite alot of my company data in the workbook now so i can share really, thanks anyway ill just have to keep trying different things until i hit lucky

Quote from Mumps

I don't know what is causing the problem. Maybe you can try adding code to make the workbook visible, print the range and then hide it again.

I did try this but being very new to VBA im not to sure if i got it write and upon searching the net dint find a great deal on it

Quote from Mumps

You could add a command button on the userform and change the caption to PRINT. In the code module for the command button insert the code to print the range when the command button is clicked.

Yep already have a cmdbutton on the userform with the code to print preview BUT when the preview comes up excel hangs and i have to shut via task manger so i was thinking this has something to do with the workbook being hidden?

After all,

I have made my workbook not visible so the user will only be able to use userforms BUT i want to have a print preview OR print button that will print off a range on a worksheet.

STUCK!

Quote from royUK

This is a separate userform to allow access to the workbook,.

Thanks Roy

A little more than what i needed and i cant work out how to use the manger funtions???

Morning All.

The project im working on ive now got it so the user only seeing the userforms i have built and they cant see the workbook BUT i want a password box on my first userform so if i want to get into the workbook i can from there.

Quote from royUK

Found some time. Try this

Morning Roy,

Sorry for that late reply i wasn't at my desk yesterday afternoon.

That seems to work great thank you so much.

I think for my first project into VBA i have started at the deep end hopefully what i have left to do i can muddle through.

Quote from royUK

Not without a source to pull from, the ListBox isn't connected to any data

Ok maybe ill have to rethink the whole form then i must have the full product code, description, price and so on in the bottom list so i can then send to a sheet to print of, email etc etc i just cant get my head round why it will only pick up the product code from my pick list??

Anyway that's why there is 6 columns i was trying to fill with all the data i need.