• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

Excel ComboBox clear values and VBA file location

After the previous question with excellent help form ScriptAddict: I’m now almost complete but need to add in a couple of amends.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27402718.html

The two workbooks are setup to run in a “C:\PO” folder but if it is possible to amend the VBA to look for Data.xlsx in the folder the PO.xlsm file is located that would be great.

From the PO.xlsm file when ‘Update’ Is clicked is copies the relevant information to the PO History sheet as it does so at the moment but also sets the ComboBox back to blank so you can start entering a purchase order for another supplier and also set the quantities back to 1 for all the items.

I’ve added in my own crude recorded macro to the ComboBox1 code that keeps the gridlines once the selection has been made as it was stripping out my formatting, if there is a cleaner way of doing that it would be good also.
Many thanks
G
 PO.xlsm
Data.xlsx
0
Glyn Merritt
Asked:
Glyn Merritt
  • 9
  • 7
1 Solution
 
Davy2270Commented:
Hi G,

I have adjusted the code so that the path being looked at is the path of the PO.xlsm workbook.
It was a simple replace of "C:\PO" by Activeworkbook.Path

I also cleaned up your RAW code.
See attach.

Regards,
Davy
PO-1-.xlsm
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Hey Davy2270

Thanks for the prompt reply. I've downloaded the file but when I open it Excel tells me there is unreadable content.

One other thing if that is ok, I forgot to mention is that the Unit price keeps setting back to general rather data type rather than currency (£ UK) is it possible to ensure that the unit price is displayed as currency?

Many thanks
Glyn
0
 
Davy2270Commented:
Maybe start by renaming the file to PO.xlsm.
The unreadable content? Perhaps you should place the file Data.xlsx in the folder where PO.xlsm is located. And refresh the datalinks. Not sure if that solves the problem, but always good to try.

If this does not work, just copy the code from my attach and put in your original file.

For ensuring you get the valuta notation add these lines at the end of your code:

Range("C10:C39").Select
Selection.NumberFormat = "[$£-809]#,##0.00"

Regards,
Davy
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Glyn MerrittIT Project ManagerAuthor Commented:
Thanks for that but I'm guessing the upload possibly went wrong?! Would you be able to test the file you uploaded?

Cheers
Glyn
0
 
Davy2270Commented:
Will do it tomorrow as I now have no access to the computer which the file resides on.
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Any joy?
0
 
Davy2270Commented:
sorry for the long delay. But here it is.
I adjusted the code for combobox 1.
Test it and see if it works for you.

Regards,
Davy
PO.xlsm
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
It appears to fail at this line after making a selection in the supplier box. Before I get the chance to make further selections.

   Set ws = xl.Workbooks(File).Worksheets(ComboBox1.Value)

The initial selection is not the issue, it is once I have selected all the items from the supplier and I want to reset the form to blank and start a new form.
0
 
Davy2270Commented:
How do you 'reset'  the form to blank?
When I choose the blank field in the dropdown box it works with no error.
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Apologies for any misunderstanding . When the form is filled in and I have printed the form I would like to on click on the update button it reset all the combo boxes to blank rather than manually change the selections.
0
 
Davy2270Commented:
Ok replace your Commit code with this one:

Sub Commit()
     
    Dim i As Long, LastRow As Long
   
    Dim ws As Worksheet
    Dim po As Worksheet
   
    Set po = Sheets("PO Form")
    Set ws = Sheets("PO History")
   
    'po.Unprotect Password:="Secret"
    'ws.Unprotect Password:="Secret"
   
    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1
   
    ws.Range("A" & LastRow) = Range("B3").Value
    ws.Range("B" & LastRow) = Range("B4").Value
    ws.Range("C" & LastRow) = Range("B6").Value
    ws.Range("D" & LastRow) = Range("B2").Value
    ws.Range("E" & LastRow) = Range("E43").Value
   
    Application.EnableEvents = False
    Range("B6").Value = ""
    Range("D10").Value = 1
    Range("D10").Copy
    Range("D10:D39").PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    Sheets("PO form").ComboBox1.Value = Null
    Application.EnableEvents = True

   
    'Range("C10,C14:C17,B23:E50").ClearContents
   
    'po.Protect Password:="Secret"
    'ws.Protect Password:="Secret"

End Sub
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Thank you. I'm unable to get to the sheet at the moment but will do tomorrow.

Cheers,
G
0
 
Glyn MerrittIT Project ManagerAuthor Commented:


Thank you.

The Commit button seems to be working well with that code but I still get:

Run-time error '9':
Subscrip out of range

On this line
 Set ws = xl.Workbooks(File).Worksheets(ComboBox1.Value)

when selecting the supplier ComboBox which means the boxes below do not populate.
0
 
Davy2270Commented:
Where are your files located?

Can you check in Private Sub ComboBox1_Change() how the Path is defined?
Path = "...."
You should place both PO and Data file into that folder.

Regards,
Davy
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Cracking! Its now working like a dream.

Thank you.
0
 
Glyn MerrittIT Project ManagerAuthor Commented:
Thank you!

We now have a fully functional PO system.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 9
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now