help with changing insert form VBA

monometh

New Member
Joined
Jul 18, 2004
Messages
29
HI,

I commissioned someone to help me develop a specific insert module for allowing users to insert particular data in a orderly fashion according to my critiera.

But I now find I need a small alteration, as in the form has many steps and has drop down boxes, but the section of the VBA code that is attached to the command button on the form which than inserts the data follows this email.

What the code does is once the command is depressed, it inserts data into two columns, minimum and maximum of the sheet labelled: "minmax" at a column depending on what the user originally selected...

eg. they first select which comapny, if company A is selected the data is inserted into min and max respectively or column D and E,

for company B, they data is inserted into F and G and so on...

it might be difficult to just see part of the code, the code below is that code which causes the insertion into the columns minimum and maximum of sheet: minmax

my Question: how to change the code below so that it is not dynamic depending on which company for just the first company.. eg. in my case, the underlying structure for company A's minimjum and maximum columns have changed, they are not side by side neightnbours. so instead of
D and E, its now D for minimum and now F for maximum ...

if the dropdown box for the other companies other than A is chosen, they are side by side...so company B, the minimum will begin now at G (after F) and max is H, so i need to keep the insertion in this manner,

if anyone is willing to help a poor sod that really needs this insertion module completed to assist his work, i can email the application for you to look at...








Private Sub CommandButton1_Click()
Workbooks("Rover Refund Small Version.xls").Sheets("minmax").Activate
MMLTCR = ActiveSheet.Cells.Find(What:="x").Row 'LeftTopCornerRow
MMLTCC = ActiveSheet.Cells.Find(What:="x").Column 'LeftTopCornerColumn
MMLBCR = ActiveSheet.Range(Cells(MMLTCR, MMLTCC), Cells(65535, MoLTCC)).End(xlDown).Row 'LeftBottomCornerRow
MMRTCC = ActiveSheet.Range(Cells(MMLTCR, MMLTCC), Cells(MoLTCR, 256)).End(xlToRight).Column 'RightTopCornerColumn
TL = UserForm2.Category.Caption
CoName = UserForm2.CompanyName.Caption
MMCCN = ActiveSheet.Rows(MMLTCR).Find(What:=CoName, LookAt:=xlWhole).Column
MMTL = ActiveSheet.Columns(MMLTCC).Find(What:=TL, LookAt:=xlWhole).Row
ActiveSheet.Cells(MMTL, MMCCN).Value = UserForm2.TextBox1.Value
ActiveSheet.Cells(MMTL, MMCCN + 1).Value = UserForm2.TextBox2.Value
Workbooks("Rover Refund Small Version.xls").Sheets("Sheet1").Activate
Unload UserForm2
End Sub
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Nimrod

MrExcel MVP
Joined
Apr 29, 2002
Messages
6,259
MMCCN = ActiveSheet.Rows(MMLTCR).Find(What:=CoName, LookAt:=xlWhole).Column
MMTL = ActiveSheet.Columns(MMLTCC).Find(What:=TL, LookAt:=xlWhole).Row
ActiveSheet.Cells(MMTL, MMCCN).Value = UserForm2.TextBox1.Value
If MMCCN = 4 Then
ActiveSheet.Cells(MMTL, MMCCN + 2).Value = UserForm2.TextBox2.Value
Else
ActiveSheet.Cells(MMTL, MMCCN + 1).Value = UserForm2.TextBox2.Value
End If
 

monometh

New Member
Joined
Jul 18, 2004
Messages
29
thanks Nimrod, I"ll give it a go, but looking at the code i'm not so certain that I've communicated it correctly my situation
 

Forum statistics

Threads
1,148,055
Messages
5,744,539
Members
423,882
Latest member
Seeham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top