麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] 儲存格內容變更後自動移到下一格 [打印本頁]

作者: owen06 時間: 2014-5-20 14:05 標題: 儲存格內容變更後自動移到下一格

請教各位大大，我這個檔案在按下『MADE』後，會產生一個範圍，範圍名稱叫『mysize』
右手邊有一堆按鈕，按下去後，activecell會等於按鈕的名字，
請問該如何設定，才能讓他在按下右邊按鈕後，mysize的第一格會產生資料，然後儲存格會依照順序往下一格移動，
例：本來在mysize.cells(1),按下按鈕輸入資料後，旗標就跳到mysize.cells(2)，再按一次按鈕，旗標就跳到mysize.cells(3)

煩請大大不吝指教，謝謝~~~

作者: yen956 時間: 2014-5-20 15:14

回復 1# owen06
試試看:
ActiveCell.Offset(1, 0).Select          '活動單元格下移一格
ActiveCell.Offset(-1, 0).Select       '活動單元格上移一格
ActiveCell.Offset(0, 1).Select          '活動單元格右移一格
ActiveCell.Offset(0, -1).Select       '活動單元格左移一格

作者: GBKEE 時間: 2014-5-20 15:49

回復 1# owen06
使用物件類別

'ThisWorkbook模組
Dim Ar() As New Class1 '物件類別模組 [名稱: Class1]
Private Sub Workbook_Open()
Dim E As OLEObject, i As Integer
With Sheet1
For Each E In .OLEObjects
If E.progID = "Forms.CommandButton.1" Then
If UCase(E.Object.Caption) <> "CLEAR" And UCase(E.Object.Caption) <> "MADE" Then
ReDim Preserve Ar(0 To i)
Set Ar(i).Class_CommandButton = E.Object
i = i + 1
End If
End If
Next
Run "Sheet1.CommandButton2_Click"
End With
End Sub

複製代碼

Public mysize As Range 'Sheet1模組
Private Sub CommandButton1_Click()
k1 = [I2].Value
k2 = [i3].Value
Set mysize = [B2].Resize(k1, k2)
mysize.clear
End Sub
Private Sub CommandButton2_Click()
'Dim mysize As Range
k1 = [I2].Value
k2 = [i3].Value
Set mysize = [B2].Resize(k1, k2)
With mysize
.Borders.LineStyle = xlContinuous
.Interior.ColorIndex = 37
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
mysize.Cells(1).Select
End Sub

複製代碼

Option Explicit '新增物件類別模組 [自動命名: Class1]
Public WithEvents Class_CommandButton As MSForms.CommandButton
Private Sub Class_CommandButton_Click()
Dim i As Integer
With Class_CommandButton.Parent.mysize
.Cells(Application.CountA(.Cells) + 1) = Class_CommandButton.Caption
End With
End Sub

複製代碼

作者: owen06 時間: 2014-5-20 16:32

回復 3# GBKEE

謝謝版主的回覆，我照你的方法設定完後，按下右邊任一按鈕，出現
執行階段錯誤：438。物件不支援此屬性或方法。
按下偵錯標示的內容為：With Class_CommandButton.Parent.mysize
麻煩你抽空再幫我看一下是哪邊設定不對，謝謝你了~

作者: owen06 時間: 2014-5-20 18:56

回復 3# GBKEE

哦，版主你好，看了你的公式之後，我想了另一個方法，輸入在sheet1裡面就成功了，謝謝你的啟發

Private Sub Worksheet_Change(ByVal Target As Range)
Dim mysize As Range
k1 = [I2].Value
k2 = [i3].Value
Set mysize = [B2].Resize(k1, k2)
If mysize.Cells(1) <> "" Then mysize.Cells(Application.CountA(mysize) + 1).Select
End Sub

複製代碼

作者: GBKEE 時間: 2014-5-21 15:05

回復 4# owen06

Public mysize As Range '模組頂端制定為此模組的公用變數,其他模組的程序可使用此變數
'Dim mysize As Range '模組頂端制定為此模組的私用變數,僅此模組的程序可使用此變數,其他模組的程序不可使用此變數
Private Sub CommandButton1_Click()
'Dim mysize As Range '此程序的私用變數,僅此程序中可用
k1 = [I2].Value
k2 = [i3].Value
Set mysize = [B2].Resize(k1, k2)
mysize.clear
End Sub
Private Sub CommandButton2_Click()
'Dim mysize As Range
k1 = [I2].Value
k2 = [i3].Value
Set mysize = [B2].Resize(k1, k2)
With mysize
.Borders.LineStyle = xlContinuous
.Interior.ColorIndex = 37
.VerticalAlignment = xlCenter
.HorizontalAlignment = xlCenter
End With
mysize.Cells(1).Select
End Sub
'後面的CommandButton??_Click 不在需要了
Private Sub CommandButton10_Click()
ActiveCell = CommandButton10.Caption
End Sub

複製代碼

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)