How to link a Module to a Userform to automate changing.

CT Chua · Mar 14, 2018

Hi
I have a spreadsheet with 2 sheet that need lot of changing, all the changes happen in Column A and B. In Sheet 1 and Sheet 2 there are 50,000 serial no. running down Column C starting from 1 to 25000 on Sheet 1 and 25001 to 50000 on Sheet 2.

On Column A, B, are Prefix and suffix to these serial no.

Column A has Prefix starting from 100 to 200 on Sheet 1 and a prefix of 201 to 300 on Sheet 2.

Column B has suffix starting from 1 and 10 for both pages. But not in constant number of row (1 could be in 10 row, 8 could be only 5 row).

Sheet 1

[TABLE="class: MsoTableGrid"]
<tbody>[TR]
[TD="width: 104"] A
[/TD]
[TD="width: 95"] B
[/TD]
[TD="width: 95"] C
[/TD]
[/TR]
[TR]
[TD="width: 104"] 100
[/TD]
[TD="width: 95"] 1
[/TD]
[TD="width: 95"] 1
[/TD]
[/TR]
[TR]
[TD="width: 104"] 100
[/TD]
[TD="width: 95"] 2
[/TD]
[TD="width: 95"] 2
[/TD]
[/TR]
[TR]
[TD="width: 104"] 101
[/TD]
[TD="width: 95"] 5
[/TD]
[TD="width: 95"] 3
[/TD]
[/TR]
[TR]
[TD="width: 104"] 101
[/TD]
[TD="width: 95"] 5
[/TD]
[TD="width: 95"] 4
[/TD]
[/TR]
[TR]
[TD="width: 104"] 155
[/TD]
[TD="width: 95"] 8
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 104"] 200
[/TD]
[TD="width: 95"] 8
[/TD]
[TD="width: 95"]
[/TD]
[/TR]
[TR]
[TD="width: 104"] 200
[/TD]
[TD="width: 95"] 9
[/TD]
[TD="width: 95"] 25000
[/TD]
[/TR]
</tbody>[/TABLE]

I will be getting the new prefix and suffix for Column A and B in a week or two.
I write a very simple module in VBA to fill A & B changes (I will need to access the code and replace the changes for A and B before running the module). My friend sometime help me out with the input but she has no idea what VBA is. I would like to make a userform so that she and myself could use but I am not sure how to link the VBA with a Userform to get it working. I am also not sure what to place in the form to effect the change. Lastly do i need one or two set of VBA and userform for this?

Code : -

Sub DoUnitilLoop() 'To change Prefix in Column A

Dim i As Integer

i = 4 '4 is starting row

Do Until i > 65 '65 is end row , so total row with Prefix 100 = 62

Cells(i, 0).Value = 100 '100 is the Prefix in Column A
i = i + 1
Loop
End Sub

Would appreciate if anyone could help out.

Thanks
Regards,

$C:\Users\CHUACH~1\AppData\Local\Temp\msohtmlclip1\01\clip_image002.png$

ranman256 · Mar 14, 2018

you dont 'link' the module. You have modules with code, then the Userform can call anything in it.
so in your example, click a button and it would call the Sub....

Rich (BB code):

sub btnGo_click()
   DoUnitilLoop
end sub

create modules in VBE (alt-F11)
menu: insert, module,
paste your code
save.

CT Chua · Mar 15, 2018

Thanks
I will try it out with your instruction. I will post again if i success or fail to get it working.
Sorry if I don't sound optimistic. That's because I only has very basic knowledge of VBA. I will surely give it a try.

Regards,

CT Chua · Mar 16, 2018

I am back with the code to solve my problem. Its not well written but it work and meet my need.
Would appreciate if you could advise what is needed to improve it.

Code :

Sub PrefixChange()
Dim PreOrSuf As Integer
Dim StartNo As Integer
Dim EndNo As Integer
Dim ssht As String
Dim i As Integer

PreOrSuf = Application.InputBox(prompt:="New Prefix is?", Type:=1)
StartNo = Application.InputBox(prompt:="Start Number Is?", Type:=1)
EndNo = Application.InputBox(prompt:="End Number Is?", Type:=1)

MsgBox "Prefix will be Change to " & PreOrSuf & " from " & StartNo & " To " & EndNo _
& " Press Ok to confirm"

i = StartNo + 4 'Starting Row

Do Until i > EndNo 'Loop to End Number
Cells(i, 1).Value = PreOrSuf 'Value of PreOrSuf
i = i + 1
Loop

End Sub

Sub Suffixchange()

Dim PreOrSuf As Integer
Dim StartNo As Integer
Dim EndNo As Integer
Dim ssht As String
Dim i As Integer

PreOrSuf = Application.InputBox(prompt:="New Suffix is?", Type:=1)
StartNo = Application.InputBox(prompt:="Start Number Is?", Type:=1)
EndNo = Application.InputBox(prompt:="End Number Is?", Type:=1)

MsgBox "Suffix will be Change to " & PreOrSuf & " from " & StartNo & " To " & EndNo _
& " Press Ok to confirm"

i = StartNo + 4 'Starting Row

Do Until i > EndNo 'Loop to End Number
Cells(i, 2).Value = PreOrSuf 'Value of PreOrSuf
i = i + 1

Loop

End Sub

Will be happy if anyone could advise on improving it.

Thanks

Regards,

CT Chua · Mar 16, 2018

Hi again,

Sorry to say, I make an error in a line code. Forgot to add 3 row back if the change is for 10 or any numbers.
Amended Code for both Prefix and Suffix as follow.

Do Until i > EndNo + 3 'Loop to End Number

Regards,

How to link a Module to a Userform to automate changing.

CT Chua

New Member

Excel Facts

ranman256

Well-known Member

CT Chua

New Member

CT Chua

New Member

CT Chua

New Member

Similar threads

Forum statistics

Share this page

How to link a Module to a Userform to automate changing.

CT Chua

New Member

Excel Facts

ranman256

Well-known Member

CT Chua

New Member

CT Chua

New Member

CT Chua

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock