Myriad of VBA questions

[Asevens]

I feel like I have been unable to deliver on my project and that Ive been asking for too much of you all but here I go again.

With much appreciation to those who have already helped, this is the code for my userform.

Public Sub Workbook_Open()
    UserForm1.Show
End Sub




Private Sub UserForm_Initialize()
    NSN.Value = ""
    LocalAsset.Value = ""
    With CarrierCombo
    .AddItem "CMTT"
    .AddItem "Day & Ross"
    .AddItem "Delivered"
    .AddItem "DHL"
    .AddItem "FedEx"
    .AddItem "Hand Delivery"
    .AddItem "Loomis"
    .AddItem "Pick-Up"
    .AddItem "Purolator"
    .AddItem "Pylon"
    .AddItem "Speedy Messenger"
    .AddItem "UPS"
    End With
    CarrierWaybill.Value = ""
    Receiver.Value = ""
    Sender.Value = ""
    With ExportControl
    .TextAlign = fmTextAlignCenter
    .TripleState = False
    End With
    Frame1.Enabled = False
    
End Sub


Private Sub ExportControl_Click()
Frame1.Enabled = ExportControl
End Sub


Private Sub Frame1_Change()
If Frame1.Enabled = True Then
    Canada.Enabled = True
    US.Enabled = True
Else
If Frame1.Enabled = False Then
    Canada.Enabled = False
    US.Enabled = False
End If
End If
End Sub




Private Sub PackIt_Click()
    With Sheets("PackingSlip")
    .Range("B20").Value = ""
    .Range("B20").Value = NSN.Text
    .Range("A20").Value = LocalAsset.Text
    .Range("C5").Value = CarrierCombo.Text
    .Range("C6").Value = CarrierWaybill.Text
    .Range("A10").Value = Sender.Text
    .Range("D10").Value = Receiver.Text
    End With
    Unload Me
End Sub

Now for the most part it works. The issue I am running into is that in the cell B20 on my worksheet, there is currently a formula which uses index to find information based on A20. In most cases the shippers know the value for the Local Asset but not necessarily the stock number. When I run my form, even with nothing in that value on the form the cell is overwritten which then leads to a lack of information.
In some cases the shipper only has the stock number instead of the asset number, so I need to insert a cyclical formula in the worksheet (i believe) so that as long as one of those numbers is input, the rest of my information is pulled.

Now, the second issue. As part of my form, there is an option to select if the item is being exported. The current system that is officially outmoded but still in operation, automatically pulls the information required from the worksheet, pastes it into appropriate tables in a word document and prints it all in one sweep. I am so far from this happening because A. I dont know how to transpose that information to a word document and B. Creating that macro for printing involves local access to the PC's used which I dont currently have.

I am in way over my head here. I thought when they initially asked that they wanted a simple database, not a new program (basically). I've been using Excel for ease of use but Im just not getting it. And VBA is an entirely new language for me so if any of the code looks sloppy that is all me. The stuff you've all provided is qreat.

I cant really offer anything in return for any help because, frankly I am a poor SOB and also the material required for anyone to build this from scratch is considered confidential ergo I cant pass it on for further help.


If anyone is able to help or at least point me in a direction to learn more... in a quick fashion, it would be very greatly appreciated.

Thanks everyone.

 

[Asevens]

I dont have access to a cloud drive atm. We operate behind a fairly hefty firewall here.

I can put up my sandbox version which is way stripped down when I get home later.

Unfortunatly I cant put all the data in there because of sensitivity issues.

 

[jproffer]

As long as the userform, some data (even fake data) and your packing slip template (headers and names removed is fine) is there, I think we can get you there. You may have to add logos, headers, etc back in.

The PS template can be as simple as a border around cells, and "A", "B", "C", "D", etc for the information labels. As long as the (for instance) NAME cell on the fake one match the NAME cell on the real one, ADDRESS cells match, CITY/ST/ZIP cells match, etc... the coding will be the same.

As far as fake data, one shipment will work...just something to test with...make sure Name, Address, etc go where they are supposed to.

BTW, work is about over so my replies wont be as quick for a few hours, but I will be back on tonight and see what we've got going on..